IN2BI, Microsoft Business Intelligence
data, stories & insights
rss | email | twitter

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:

image

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:

image

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:

image

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:

image

» Similar Posts

  1. SSIS Pattern: Drop- and rebuild indexes dynamically
  2. SQL2012 Windowing Functions In The Data Warehouse–1. Dimensions
  3. Create a LightSwitch Application to Maintain Metadata

» Trackbacks & Pingbacks

    No trackbacks yet.

» Comments

    There are no comments.

Comments are closed