Get columns of the columns table

user_6436191 0
Viewed 116

How can I get the list of columns of the INFORMATION_SCHEMA.COLUMNS view?

I can get the columns of any table I create using:

select *
from information_schema.columns
where table_name = 'MY_TABLE'

The result set includes multiple columns, such as:

  • TABLE_CATALOG
  • TABLE_SCHEMA
  • TABLE_NAME
  • COLUMN_NAME
  • DATA_TYPE
  • etc.

How can I get the full list of these columns? It seems that the COLUMNS view does not include its columns in itself (as in Oracle, PostgreSQL, or MySQL). If I run:

select * 
from information_schema.columns
where table_name = 'COLUMNS'

I get nothing. Maybe I'm searching in the wrong place.

sql-server-2014 metadata sql
2 Answers
user_50708790

SQL Server hold its metadata under the sys schema.
INFORMATION_SCHEMA was added later on as part of the SQL ISO complience. 

select   s.name as schema_name
        ,o.name as table_name
        ,c.name as column_name
        ,t.name as type_name
from         sys.schemas        s
        join sys.all_objects    o on o.schema_id = s.schema_id
        join sys.all_columns    c on c.object_id = o.object_id
        join sys.types          t on t.system_type_id = c.system_type_id and t.user_type_id = c.user_type_id
where   s.name = 'INFORMATION_SCHEMA'
    and o.name = 'COLUMNS'

.

+--------------------+------------+--------------------------+-----------+
|    schema_name     | table_name |       column_name        | type_name |
+--------------------+------------+--------------------------+-----------+
| INFORMATION_SCHEMA | COLUMNS    | TABLE_CATALOG            | nvarchar  |
| INFORMATION_SCHEMA | COLUMNS    | TABLE_SCHEMA             | nvarchar  |
| INFORMATION_SCHEMA | COLUMNS    | TABLE_NAME               | sysname   |
| INFORMATION_SCHEMA | COLUMNS    | COLUMN_NAME              | sysname   |
| INFORMATION_SCHEMA | COLUMNS    | ORDINAL_POSITION         | int       |
| INFORMATION_SCHEMA | COLUMNS    | COLUMN_DEFAULT           | nvarchar  |
| INFORMATION_SCHEMA | COLUMNS    | IS_NULLABLE              | varchar   |
| INFORMATION_SCHEMA | COLUMNS    | DATA_TYPE                | nvarchar  |
| INFORMATION_SCHEMA | COLUMNS    | CHARACTER_MAXIMUM_LENGTH | int       |
| INFORMATION_SCHEMA | COLUMNS    | CHARACTER_OCTET_LENGTH   | int       |
| INFORMATION_SCHEMA | COLUMNS    | NUMERIC_PRECISION        | tinyint   |
| INFORMATION_SCHEMA | COLUMNS    | NUMERIC_PRECISION_RADIX  | smallint  |
| INFORMATION_SCHEMA | COLUMNS    | NUMERIC_SCALE            | int       |
| INFORMATION_SCHEMA | COLUMNS    | DATETIME_PRECISION       | smallint  |
| INFORMATION_SCHEMA | COLUMNS    | CHARACTER_SET_CATALOG    | sysname   |
| INFORMATION_SCHEMA | COLUMNS    | CHARACTER_SET_SCHEMA     | sysname   |
| INFORMATION_SCHEMA | COLUMNS    | CHARACTER_SET_NAME       | sysname   |
| INFORMATION_SCHEMA | COLUMNS    | COLLATION_CATALOG        | sysname   |
| INFORMATION_SCHEMA | COLUMNS    | COLLATION_SCHEMA         | sysname   |
| INFORMATION_SCHEMA | COLUMNS    | COLLATION_NAME           | sysname   |
| INFORMATION_SCHEMA | COLUMNS    | DOMAIN_CATALOG           | sysname   |
| INFORMATION_SCHEMA | COLUMNS    | DOMAIN_SCHEMA            | sysname   |
| INFORMATION_SCHEMA | COLUMNS    | DOMAIN_NAME              | sysname   |
+--------------------+------------+--------------------------+-----------+
user_63364790
Related