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:
Summary List - containing schema/object names and any stored extended properties. Ideal for documentation
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