Cool Tips‎ > ‎Tables and Triggers‎ > ‎

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 Documentation FROM sys.objects AS ob LEFT OUTER JOIN sys.extended_properties AS EP ON EP.major_id = ob.object_id AND 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 SELECT ob.name AS System_Table_Name ,COALESCE(EP.value, '') AS Documentation FROM sys.objects AS ob LEFT OUTER JOIN sys.extended_properties AS EP ON EP.major_id = ob.object_id AND 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 SELECT ob.name AS User_Table ,COALESCE(ep.value, '') AS Documentation FROM sys.objects AS ob LEFT OUTER JOIN sys.extended_properties AS ep ON ep.major_id = ob.object_id AND ep.class = 1 AND ep.minor_id = 0 WHERE 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_Count FROM sys.partitions p INNER JOIN sys.tables t ON p.object_ID=t.object_ID WHERE index_id < 2 --there will either be entries with index id 0 (heap) or 1 (clustered index) GROUP BY t.object_ID,Index_ID ORDER 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_count FROM sys.indexes i INNER JOIN ( SELECT object_ID ,Index_ID ,sum(rows) AS Row_count FROM sys.partitions GROUP BY object_ID ,Index_ID ) AS f ON f.object_ID=i.object_ID AND f.index_ID=i.index_ID INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id WHERE objectproperty(i.object_id, 'IsUserTable') = 1 GROUP BY i.object_id ORDER BY Table_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_Type FROM sys.objects child INNER JOIN sys.objects parent ON parent.object_ID=child.parent_object_id WHERE child.parent_object_id <> 0 ORDER BY Parent_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 1 ELSE 0 END AS Table_Has_No_Primary_Key ,CASE -- tables by name that have no indexes at all WHEN OBJECTPROPERTY(OBJECT_ID,'TableHasIndex') = 0 THEN 1 ELSE 0 END 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') = 0 AND OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0) THEN 1 ELSE 0 END 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 1 ELSE 0 END AS Table_Has_Disabled_Indexes FROM 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_Columns FROM sys.columns c INNER JOIN sys.tables t ON c.object_ID = t.object_id GROUP BY OBJECT_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_Columnstore FROM sys.indexes a INNER JOIN sys.tables t ON a.object_ID = t.object_id WHERE OBJECT_SCHEMA_NAME(a.object_ID) <> 'sys' -- and a.name is not null GROUP BY OBJECT_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 SYSNAME SELECT -- @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 , CASE WHEN t.name IN ('char', 'varchar','nchar','nvarchar') THEN '(' --we fetch the length of the data + CASE WHEN col.max_length=-1 THEN 'MAX' ELSE CONVERT(VARCHAR(4), --get the length CASE WHEN t.name IN ('nchar','nvarchar') THEN col.max_length/2 ELSE col.max_length END ) 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_Modifier FROM 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_ID INNER JOIN sys.types t --to get the details of the types ON col.user_type_id=t.user_type_id WHERE obj.object_ID = ISNULL(object_id(@TableName), obj.object_ID) ORDER BY Table_Name ,column_ID




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

 --get tables with more than n columns 

DECLARE @NumberOfColumns INT SELECT -- @NumberOfColumns = NULL @NumberOfColumns = 15 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 ,COUNT(*) AS Number_of_Columns FROM sys.columns c INNER JOIN sys.tables t ON c.object_id = t.object_id GROUP BY t.object_id HAVING COUNT(*) > ISNULL(@NumberOfColumns, 0) ORDER BY Number_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 SYSNAME SELECT -- @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_Type FROM sys.All_Columns c INNER JOIN sys.all_Objects o ON c.object_ID=o.object_ID WHERE c.name LIKE ISNULL(@ColumnName, c.name) ORDER BY Object_Name