Get Table Metadata

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Not Tested

Credits:

Author:Phil Factor/ChillyDBA

Date: 5 Jun 2020

Description

A small collection of queries to produce database-level reports on tables. These should mostly run as-is but can be easily customized.

There are 2 main types of report queries:

    1. Summary List - containing schema/object names and any stored extended properties. Ideal for documentation

    2. Detail List - containing schema/object names plus object type, component columns and code. Can be used for documentation, but also valuable for use when constructing Dynamic SQL queries

Where there are multiple variants on the same query, one will utilize the INFORMATION_SCHEMA views and the other the Dynamic Management Views. This is generally for Detail type queries as some detail levels are only available in one source

Code

Get all Tables:

--all tables, including system tables, in the database

SELECT

ob.name AS Table_Name --includes system tables etc ,LOWER(REPLACE(type_desc,'_',' ')) AS Table_Type --the type of constraint ,COALESCE(EP.value, '') AS DocumentationFROM sys.objects AS obLEFT OUTER JOIN sys.extended_properties AS EPON EP.major_id = ob.object_idAND EP.name = 'MS_Description' --the microsoft convention WHERE OBJECTPROPERTY(ob.object_id, 'IsTable') = 1;

Get all System Tables:

/* The System Tables */

--all system tables in the database SELECTob.name AS System_Table_Name,COALESCE(EP.value, '') AS DocumentationFROM sys.objects AS obLEFT OUTER JOIN sys.extended_properties AS EPON EP.major_id = ob.object_idAND EP.name = 'MS_Description' --the microsoft convention WHERE OBJECTPROPERTY(ob.object_id, 'IsSystemTable') = 1;

Get all User Tables:

/* The Tables */

--all user tables in the database SELECTob.name AS User_Table,COALESCE(ep.value, '') AS DocumentationFROM sys.objects AS obLEFT OUTER JOIN sys.extended_properties AS epON ep.major_id = ob.object_idAND ep.class = 1AND ep.minor_id = 0WHERE OBJECTPROPERTY(ob.object_id, 'IsUserTable') = 1

Get all Tables Summary with Row Count:

--get table rowcounts SELECT @@SERVERNAME AS Server_Name,DB_NAME() AS Database_Name,OBJECT_SCHEMA_NAME(t.object_ID) AS Schema_Name,OBJECT_NAME(t.object_ID) AS Table_Name,OBJECT_SCHEMA_NAME(t.object_id) +'.' + OBJECT_NAME(t.object_id) AS Combined_Name,SUM(rows) AS Row_CountFROM sys.partitions pINNER JOIN sys.tables tON p.object_ID=t.object_IDWHERE index_id < 2 --there will either be entries with index id 0 (heap) or 1 (clustered index) GROUP BY t.object_ID,Index_IDORDER BY OBJECT_NAME(t.object_ID)

Get all Tables with Index Details and Row Counts plus total Index size:

--tables, index space for each table and #rows in table

SELECT @@SERVERNAME AS Server_Name,DB_NAME() AS Database_Name,OBJECT_SCHEMA_NAME(i.object_ID) AS Schema_Name,OBJECT_NAME(i.object_ID) AS Table_Name,OBJECT_SCHEMA_NAME(i.object_id) +'.' + OBJECT_NAME(i.object_id) AS Combined_Name,CONVERT(DECIMAL(9,2),(SUM(a.total_pages) * 8.00) / 1024.00) AS Total_Index_Size_MB,MAX(row_count) AS Table_Row_Count,COUNT(*) AS Table_Index_countFROM sys.indexes iINNER JOIN(SELECTobject_ID,Index_ID,sum(rows) AS Row_countFROM sys.partitionsGROUP BYobject_ID,Index_ID) AS fON f.object_ID=i.object_IDAND f.index_ID=i.index_IDINNER JOIN sys.partitions pON i.object_id = p.object_idAND i.index_id = p.index_idINNER JOIN sys.allocation_units aON p.partition_id = a.container_idWHERE objectproperty(i.object_id, 'IsUserTable') = 1GROUP BY i.object_idORDER BYTable_Name

Get all Tables with details of all related (child) objects:

-- objects hierarchy a.k.a triggers and constraints belonging to tables

SELECT @@SERVERNAME AS Server_Name,DB_NAME() AS Database_Name,OBJECT_SCHEMA_NAME(parent.object_ID) AS Parent_Schema_Name,OBJECT_NAME(parent.object_ID) AS Parent_Object_Name,REPLACE(LOWER(parent.type_desc),'_',' ') AS Parent_Type,OBJECT_SCHEMA_NAME(child.object_ID) AS Child_Schema_Name,OBJECT_NAME(child.object_ID) AS Child_Object_Name,REPLACE(LOWER(child.type_desc),'_',' ') AS Child_TypeFROM sys.objects childINNER JOIN sys.objects parentON parent.object_ID=child.parent_object_idWHERE child.parent_object_id <> 0ORDER BYParent_Object_Name,Child_Type,Child_Object_Name

Get Summary of all Tables with Index/DRI issues:

--- list all tables and highlight those with Index issues

SELECT @@SERVERNAME AS Server_Name,DB_NAME() AS Database_Name,OBJECT_SCHEMA_NAME(t.object_ID) AS Schema_Name,OBJECT_NAME(t.object_ID) AS Table_Name,OBJECT_SCHEMA_NAME(t.object_id) +'.' + OBJECT_NAME(t.object_id) AS Combined_Name,CASE --tables that have no primary key WHEN OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0 THEN 1ELSE 0END AS Table_Has_No_Primary_Key,CASE -- tables by name that have no indexes at all WHEN OBJECTPROPERTY(OBJECT_ID,'TableHasIndex') = 0 THEN 1ELSE 0END AS Table_Has_No_Indexes,CASE -- tables by name that have no candidate key (enforced unique set of columns) /* if no unique constraint then it isn't relational */WHEN (OBJECTPROPERTY(OBJECT_ID,'TableHasUniqueCnst') = 0AND OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0) THEN 1ELSE 0END AS Table_Has_No_Candidate_Key,CASE --tables with disabled indexes /* don't leave these lying around */WHEN (SELECT SUM(CAST(is_disabled AS INT)) FROM sys.indexes i WHERE i.object_id = t.object_id) > 0 THEN 1ELSE 0END AS Table_Has_Disabled_IndexesFROM sys.tables t

Get Summary of key column types for all Tables:

--- list the number of Nullable, Computed, Replicated, Sparse and XML columns per table

SELECT

@@SERVERNAME AS Server_Name,DB_NAME() AS Database_Name,OBJECT_SCHEMA_NAME(t.object_ID) AS Schema_Name,OBJECT_NAME(t.object_ID) AS Table_Name,OBJECT_SCHEMA_NAME(t.object_ID) +'.' + OBJECT_NAME(t.object_ID) AS Combined_Name,MAX(column_ID) AS [total], SUM(CONVERT(INT, c.is_nullable)) AS Nullable_Columns,SUM(CONVERT(INT, c.is_computed)) AS Computed_Columns,SUM(CONVERT(INT, c.is_replicated)) AS Replicated_Columns,SUM(CONVERT(INT, c.is_sparse)) AS Sparse_Columns,SUM(CONVERT(INT, c.is_xml_document)) AS XML_ColumnsFROM sys.columns cINNER JOIN sys.tables tON c.object_ID = t.object_idGROUP BYOBJECT_SCHEMA_NAME(t.object_ID),OBJECT_NAME(t.object_ID),OBJECT_SCHEMA_NAME(t.object_ID) +'.' + OBJECT_NAME(t.object_ID)ORDER BY total DESC;

Get Summary of key Index types for all Tables:

--- a more comprehensive list of table/indexattributes per table

SELECT

@@SERVERNAME AS Server_Name,DB_NAME() AS Database_Name,OBJECT_SCHEMA_NAME(t.object_ID) AS Schema_Name,OBJECT_NAME(t.object_ID) AS Table_Name,OBJECT_SCHEMA_NAME(t.object_ID) +'.' + OBJECT_NAME(t.object_ID) AS Combined_Name,SUM(CASE WHEN a.name IS NULL THEN 0 ELSE 1 END) AS Total_Indexes,SUM(CASE WHEN a.is_unique <> 0 THEN 1 ELSE 0 END) AS Unique_Indexes,SUM(CASE WHEN a.is_unique_constraint <> 0 THEN 1 ELSE 0 END) AS Unique_Key,SUM(CASE WHEN a.is_primary_key <> 0 THEN 1 ELSE 0 END ) AS Primary_Key,SUM(CASE WHEN a.type = 1 THEN 1 ELSE 0 END ) AS Clustered_Indexes,SUM(CASE WHEN a.type = 2 THEN 1 ELSE 0 END ) AS NonClustered_Indexes,SUM(CASE WHEN a.type = 3 THEN 1 ELSE 0 END ) AS XML,SUM(CASE WHEN a.type = 4 THEN 1 ELSE 0 END ) AS Spatial,SUM(CASE WHEN a.type = 5 THEN 1 ELSE 0 END ) AS Clustered_Columnstore,SUM(CASE WHEN a.type = 6 THEN 1 ELSE 0 END ) AS Nonclustered_ColumnstoreFROM sys.indexes aINNER JOIN sys.tables tON a.object_ID = t.object_idWHERE OBJECT_SCHEMA_NAME(a.object_ID) <> 'sys'-- and a.name is not null GROUP BYOBJECT_SCHEMA_NAME(t.object_ID),OBJECT_NAME(t.object_ID),OBJECT_SCHEMA_NAME(t.object_ID) +'.' + OBJECT_NAME(t.object_ID)

Get Details of all Tables with Columns and Data Types:

--get tables and columns DECLARE @TableName SYSNAMESELECT-- @TableName = NULL @TableName = 'addresses'SELECT @@SERVERNAME AS Server_Name,DB_NAME() AS Database_Name,OBJECT_SCHEMA_NAME(obj.object_ID) AS Schema_Name,OBJECT_NAME(obj.object_ID) AS Table_Name,OBJECT_SCHEMA_NAME(obj.object_ID) +'.' + OBJECT_NAME(obj.object_ID) AS Combined_Name,column_ID AS Column_Ordinal,col.name AS Column_Name,t.name AS Column_DataType, CASEWHEN t.name IN ('char', 'varchar','nchar','nvarchar') THEN '(' --we fetch the length of the data + CASEWHEN col.max_length=-1 THEN 'MAX'ELSE CONVERT(VARCHAR(4), --get the length CASEWHEN t.name IN ('nchar','nvarchar') THEN col.max_length/2ELSE col.max_lengthEND )END +')'WHEN t.name IN ('decimal','numeric') --we need scale and precision THEN '('+ convert(VARCHAR(4),col.precision)+',' + convert(VARCHAR(4),col.Scale)+')'ELSE ''END AS Column_DataType_ModifierFROM sys.all_objects obj -- from all the objects (system and database) INNER JOIN sys.all_columns col --to get all the columns ON col.object_ID=obj.object_IDINNER JOIN sys.types t --to get the details of the types ON col.user_type_id=t.user_type_idWHERE obj.object_ID = ISNULL(object_id(@TableName), obj.object_ID)ORDER BYTable_Name,column_ID

Get all Tables that have more than a (parameter) number of columns :

--get tables with more than n columns

DECLARE @NumberOfColumns INTSELECT-- @NumberOfColumns = NULL @NumberOfColumns = 15SELECT @@SERVERNAME AS Server_Name,DB_NAME() AS Database_Name,OBJECT_SCHEMA_NAME(t.object_ID) AS Schema_Name,OBJECT_NAME(t.object_ID) AS Table_Name,OBJECT_SCHEMA_NAME(t.object_ID) +'.' + OBJECT_NAME(t.object_ID) AS Combined_Name,COUNT(*) AS Number_of_ColumnsFROM sys.columns cINNER JOIN sys.tables tON c.object_id = t.object_idGROUP BY t.object_idHAVING COUNT(*) > ISNULL(@NumberOfColumns, 0)ORDER BYNumber_of_Columns DESC,Table_Name

Get list of Tables containing a Columnb with a (parameterized) Column Name

--get all occurrences of a specified column in a database

DECLARE @ColumnName SYSNAMESELECT-- @ColumnName = NULL @ColumnName = '%' + 'create_date' + '%'SELECT @@SERVERNAME AS Server_Name,DB_NAME() AS Database_Name,OBJECT_SCHEMA_NAME(o.object_ID) AS Schema_Name,OBJECT_NAME(o.object_ID) AS Object_Name,OBJECT_SCHEMA_NAME(o.object_ID) +'.' + OBJECT_NAME(o.object_ID) AS Combined_Name,c.name AS Column_Name,LOWER(REPLACE(o.type_desc,'_',' ')) AS Object_TypeFROM sys.All_Columns cINNER JOIN sys.all_Objects oON c.object_ID=o.object_IDWHERE c.name LIKE ISNULL(@ColumnName, c.name)ORDER BY Object_Name