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:

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:

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