TSQL Metadata Queries
When you want to query SQL Server for the metadata you basically have two options:
- Use the Catalog views
- Use the Information Schema Views
The last one are based on catalog view definitions in the ISO standard. You typically use them if you need to support more database systems.
The first is one is recommended by Microsoft.
In this blogpost I recapitulate some queries that I have used in the past and will probably use in the future.
1. Get column information from all tables
-----------------------------------------------------------
-- Query: GetColumnInfoFromAllTables
-- By Marco Schreuder 27-09-2011
-----------------------------------------------------------
SELECT SchemaName = SCHEMA_NAME(T.schema_id)
,TableName = T.name
,ColumnName = C.name
,ColumnID = C.column_id
,DataType = ty.name
,MaxLength = C.max_length
,PRECISION = C.PRECISION
,Scale = C.scale
,CondensedDataType=(CASE
WHEN (ty.name IN ('char','nchar','varchar','nvarchar') AND C.max_length=-1)
THEN ty.name + '(MAX)'
WHEN (ty.name IN ('char','nchar','varchar','nvarchar') AND C.max_length>0)
THEN ty.name + '(' + CONVERT(VARCHAR(20),C.max_length) + ')'
WHEN ty.name IN ('decimal','numeric')
THEN ty.name + '(' + CONVERT(VARCHAR(20),C.PRECISION) + ',' +
CONVERT(VARCHAR(20),C.scale) + ')'
WHEN ty.name IN ('datetime2','datetimeoffset')
THEN ty.name + '(' + CONVERT(VARCHAR(20),C.scale) + ')'
ELSE ty.name
END )
,IsIdentity = c.is_identity
,IsNullable = c.is_nullable
,ModifiedDate = T.modify_date
FROM sys.tables T
INNER JOIN sys.columns C ON C.OBJECT_ID=T.OBJECT_ID
INNER JOIN sys.types ty ON C.system_type_id=ty.user_type_id
ORDER BY SCHEMA_NAME(T.schema_id),T.Name,C.column_id
This will return this result set:
Notice the join between sys.types and sys.columns on type.user_type_id. Otherwise you would get duplicate rows if there are user types defined.
2. Get column information from all views
Almost the same as 1. Replacing sys.tables with sys.views:
-----------------------------------------------------------
-- Query: GetColumnInfoFromAllViews
-- By Marco Schreuder 27-09-2011
-----------------------------------------------------------
SELECT SchemaName = SCHEMA_NAME(V.schema_id)
,ViewName = V.name
,ColumnName = C.name
,ColumnID = C.column_id
,DataType = ty.name
,MaxLength = C.max_length
,PRECISION = C.PRECISION
,Scale = C.scale
,CondensedDataType=(CASE
WHEN (ty.name IN ('char','nchar','varchar','nvarchar') AND C.max_length=-1)
THEN ty.name + '(MAX)'
WHEN (ty.name IN ('char','nchar','varchar','nvarchar') AND C.max_length>0)
THEN ty.name + '(' + CONVERT(VARCHAR(20),C.max_length) + ')'
WHEN ty.name IN ('decimal','numeric') THEN ty.name + '(' + CONVERT(
VARCHAR(20),C.PRECISION) + ',' + CONVERT(VARCHAR(20),C.scale) + ')'
WHEN ty.name IN ('datetime2','datetimeoffset')
THEN ty.name + '(' + CONVERT(VARCHAR(20),C.scale) + ')'
ELSE ty.name
END )
,IsIdentity = c.is_identity
,IsNullable = c.is_nullable
,ModifiedDate = V.modify_date
FROM sys.views V
INNER JOIN sys.columns C ON C.OBJECT_ID=V.OBJECT_ID
INNER JOIN sys.types ty ON C.system_type_id=ty.user_type_id
ORDER BY V.Name,C.column_id
Resulting in:
3. Get details of foreign key constraints
-----------------------------------------------------------
-- Query: GetFKDetails
-- By Marco Schreuder 27-09-2011
-----------------------------------------------------------
SELECT FKName = f.name
,FKSchema = OBJECT_SCHEMA_NAME(f.parent_object_id)
,FKTable = OBJECT_NAME(f.parent_object_id)
,FKColumn = COL_NAME(f.parent_object_id,fc.parent_column_id)
,RefSchema = OBJECT_SCHEMA_NAME(f.referenced_object_id)
,RefTable = OBJECT_NAME(f.referenced_object_id)
,RefColumn = COL_NAME(fc.referenced_object_id,fc.referenced_column_id)
,ConstraintColumnID = fc.constraint_column_id
,ModifiedDate = f.modify_date
FROM sys.foreign_keys f
INNER JOIN sys.foreign_key_columns fc
ON f.OBJECT_ID = fc.constraint_object_id returns:
4. Get details of the indices created on user tables
-----------------------------------------------------------
-- Query: GetIndexDetails
-- By Marco Schreuder 27-09-2011
-----------------------------------------------------------
SELECT IndexName = i.name
,SchemaName = OBJECT_SCHEMA_NAME(i.OBJECT_ID)
,ObjectName = OBJECT_NAME(i.OBJECT_ID)
,ColumnName = COL_NAME(i.OBJECT_ID,ic.column_id)
,IndexType = i.type_desc
,IndexID = i.index_id
,IndexColumnID = ic.index_column_id
,KeyOrdinal = ic.key_ordinal
,IsIncludedColumn = ic.is_included_column
,IsUnique = i.is_unique
,IsPrimaryKey = i.is_primary_key
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id=ic.index_id
--limit to indexes on user tables
INNER JOIN sys.tables t
ON i.OBJECT_ID = t.OBJECT_ID
which returns:
