Home / server / Questions / 1155571
Accepted
questionto42
Asked: 2024-03-03 00:29:50 +0800 CST

How do I get something like the INFORMATION_SCHEMA.COLUMNS metadata view of the INFORMATION_SCHEMA.COLUMNS view itself?

  • 772

All columns of the INFORMATION_SCHEMA.COLUMNS view are listed at Learn -> SQL -> SQL Server -> COLUMNS (Transact-SQL)::

enter image description here

COLUMN_NAME is of data type nvarchar(128):

enter image description here

But there is no more metadata about this column. Which collation does it have?

Where is the documentation for all of the attributes of this column:

  • on the Microsoft sites?

  • or perhaps in some SQL Server menu itself? I have Server Management Studio. With DBeaver on a MySQL database, you can check the properties of any view or table with a right click so that you can see all of the column attributes. But MS SQL Server does not have this.

  • Or how can I ask for the INFORMATION_SCHEMA.COLUMNS view of the INFORMATION_SCHEMA.COLUMNS view? Something like:

    select * from INFORMATION_SCHEMA.COLUMNS where c.TABLE_SCHEMA = 
'INFORMATION_SCHEMA' and c.TABLE_NAME = 'COLUMNS'
sql-server

2 Answers

  1. Best Answer

    You can open a reader and then get the column schema with GetColumnSchema(). Here is a C# example:

    using var connection = new SqlConnection(ConnectionString);
using var command =
    new SqlCommand("SELECT TOP 1 * FROM INFORMATION_SCHEMA.COLUMNS", connection);
connection.Open();
using var reader = command.ExecuteReader();
foreach (DbColumn column in reader.GetColumnSchema()) {
    Console.WriteLine($"{column.ColumnName} {column.DataTypeName}({column.ColumnSize})");
}
Console.ReadKey();

    Prints:

    TABLE_CATALOG nvarchar(128)
TABLE_SCHEMA nvarchar(128)
TABLE_NAME nvarchar(128)
COLUMN_NAME nvarchar(128)
ORDINAL_POSITION int(4)
COLUMN_DEFAULT nvarchar(4000)
IS_NULLABLE varchar(3)
DATA_TYPE nvarchar(128)
CHARACTER_MAXIMUM_LENGTH int(4)
CHARACTER_OCTET_LENGTH int(4)
NUMERIC_PRECISION tinyint(1)
NUMERIC_PRECISION_RADIX smallint(2)
NUMERIC_SCALE int(4)
DATETIME_PRECISION smallint(2)
CHARACTER_SET_CATALOG nvarchar(128)
CHARACTER_SET_SCHEMA nvarchar(128)
CHARACTER_SET_NAME nvarchar(128)
COLLATION_CATALOG nvarchar(128)
COLLATION_SCHEMA nvarchar(128)
COLLATION_NAME nvarchar(128)
DOMAIN_CATALOG nvarchar(128)
DOMAIN_SCHEMA nvarchar(128)
DOMAIN_NAME nvarchar(128)

    You can run this C# code for example from Visual Studio with a Visual C# "ConsoleApp (.Net Framework)" project:

    enter image description here

    Which gives you a standard empty project code from where you can go on:

    enter image description here

    You can also find the INFORMATION view itself under Views > System Views in SSMS: enter image description here

    • 1

  2. I think I've got what you're looking for by going to the native mssql DMVs. Note - I had to use the "all_" versions of them (which include system objects); I think this is why information_schema.columns isn't showing up in your original query:

    select c.*
from sys.all_columns as c
join sys.all_objects as o
    on o.object_id = c.object_id
where o.schema_id = schema_id('INFORMATION_SCHEMA')
    and o.name = 'COLUMNS';
    • 1