Retrieve SQL Server Metadata using T-SQL (MS SQL)
Using a set of metadata INFORMATION_SCHEMA queries (first made available in Microsoft SQL Server 7) you can easily retrieve information about the tables, columns, and other objects within a database. This brief how-to will cover how to retrieve all columns in the database, primary key columns, a list of constraints, and a list of tables.
All Columns in Database
Returns all columns within the database (called a Catalog) as well as some of its settings like position and data type.
select * from information_schema.COLUMNS
Returns the following columns:
- TABLE_CATALOG
- TABLE_SCHEMA
- TABLE_NAME
- COLUMN_NAME
- ORDINAL_POSITION
- COLUMN_DEFAULT
- IS_NULLABLE
- DATA_TYPE
- CHARACTER_MAXIMUM_LENGTH
- CHARACTER_OCTET_LENGTH
- NUMERIC_PRECISION
- NUMERIC_PRECISION_RADIX
- NUMERIC_SCALE
- DATETIME_PRECISION
- CHARACTER_SET_CATALOG
- CHARACTER_SET_SCHEMA
- CHARACTER_SET_NAME
- COLLATION_CATALOG
- COLLATION_SCHEMA
- COLLATION_NAME
- DOMAIN_CATALOG
- DOMAIN_SCHEMA
- DOMAIN_NAME
Primary Keys
Lists all primary keys set within the catalog. Primary keys set constraints so the output of this query will be similar to the list of constraints.
select * from information_schema.KEY_COLUMN_USAGE
Returns the following columns:
- CONSTRAINT_CATALOG
- CONSTRAINT_SCHEMA
- CONSTRAINT_NAME
- TABLE_CATALOG
- TABLE_SCHEMA
- TABLE_NAME
- COLUMN_NAME
- ORDINAL_POSITION
Constraints
Returns a list of all constraints in the catalog.
select * from information_schema.TABLE_CONSTRAINTS
Returns the following columns:
- CONSTRAINT_CATALOG
- CONSTRAINT_SCHEMA
- CONSTRAINT_NAME
- TABLE_CATALOG
- TABLE_SCHEMA
- TABLE_NAME
- CONSTRAINT_TYPE
- IS_DEFERRABLE
- INITIALLY_DEFERRED
List of Tables
Displays a list of tables within the catalog.
select * from information_schema.tables
Returns the following columns:
- TABLE_CATALOG
- TABLE_SCHEMA
- TABLE_NAME
- TABLE_TYPE
Since this is just a query after all, we can limit the results to just the table names and not select any additional columns.
select TABLE_NAME from information_schema.tables
