Table Profile

The following SQL Server code will create a "Table_Profile" that shows the following table metadata and static & dynamic volumetrics: 

• Table Owner 
• Schema Name 
• Table Name 
• Number of Columns 
• Number of Rows 
• Size (MB) 
• Has Primary Key 
• Has Identity Column 
• Has Unique Constraint 
• Has Cluster Index 
• Has an Index 
• Has a Foreign Key Constraint to a Parent Table 
• Is Referenced by other Child tables having a Foreign Key Constraint 
• Number of Referenced Parent Tables 
• Number of Referenced Child Tables 
• Has Check Constraint 
• Has Text Image (Blob) 
• Has Active Full Text Index 
• Has Row Guid Column 
• Has Timestamp 
• Has Trigger 
• Number of Index Seeks 
• Number of Index Scans 
• Number of Index Lookups 
• Number of Index Updates 
• Create Date 
• Modify Date 
• Last User Update 
• Last User Index Read 
• Extended Property Name 
• Extended Property Value (This is a table description you can assign using EXEC sys.sp_addextendedproperty)

 --Display table metadata and static & dynamic volumetrics.
SELECT 
  COALESCE((SELECT pr.name 		
            FROM sys.database_principals pr 		
            WHERE pr.principal_id = tbl.principal_id)		
           , SCHEMA_NAME(tbl.schema_id)) AS [Owner]		
, SCHEMA_NAME(tbl.schema_id) as [Schema]
, tbl.Name		
, tbl.max_column_id_used as [Columns]
--, (SELECT COUNT(*) 
--	FROM INFORMATION_SCHEMA.COLUMNS a
--	WHERE a.table_schema = SCHEMA_NAME(tbl.schema_id) AND a.table_name = tbl.Name ) AS [#Columns] 
, Coalesce( (Select sum (spart.rows) from sys.partitions spart 		
    Where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [Rows]		
, "Size (MB)"
, OBJECTPROPERTY(tbl.object_id, 'TableHasPrimaryKey') AS [PK]
, OBJECTPROPERTY(tbl.object_id, 'TableHasIdentity')   AS [Identity Col]
, OBJECTPROPERTY(tbl.object_id, 'TableHasUniqueCnst') AS [Unique Cnt]
, OBJECTPROPERTY(tbl.object_id, 'TableHasClustIndex') AS [ClustIdx]
, OBJECTPROPERTY(tbl.object_id, 'TableHasIndex')      AS [Indexed]
, OBJECTPROPERTY(tbl.object_id, 'TableHasForeignKey') AS [FK Cnt]
, OBJECTPROPERTY(tbl.object_id, 'TableHasForeignRef') AS [FK Ref]
, Coalesce(A.Parents, 0) Parents
, Coalesce(C.Children, 0 ) Children
, OBJECTPROPERTY(tbl.object_id, 'TableHasCheckCnst')  AS [Check Cnt]
, OBJECTPROPERTY(tbl.object_id, 'TableHasTextImage')  AS [Blob]
, OBJECTPROPERTY(tbl.object_id, 'TableHasActiveFulltextIndex') AS [FT index]
, OBJECTPROPERTY(tbl.object_id, 'TableHasRowGuidCol') AS [ROWGUIDCOL]
, OBJECTPROPERTY(tbl.object_id, 'TableHasTimestamp')  AS [Timestamp]
, CASE 
  WHEN OBJECTPROPERTY(tbl.object_id, 'HasDeleteTrigger') = 1 THEN 1 
  WHEN OBJECTPROPERTY(tbl.object_id, 'HasInsertTrigger') = 1 THEN 1
  WHEN OBJECTPROPERTY(tbl.object_id, 'HasUpdateTrigger') = 1 THEN 1
  WHEN OBJECTPROPERTY(tbl.object_id, 'HasAfterTrigger')  = 1 THEN 1
  WHEN OBJECTPROPERTY(tbl.object_id, 'HasInsteadOfTrigger') = 1 THEN 1
  ELSE 0
  END AS [Trigger]
, L.index_seeks [Idx Seeks], L.index_scans [Idx Scan], L.index_lookups [Idx Lookups], L.index_updates [Idx Updates]		
, tbl.create_date, tbl.modify_date		
, L.last_user_update		
, L.last_user_index_read		
, P.name AS ExtendedPropertyName		
, CAST(P.value AS sql_variant) AS ExtendedPropertyValue
INTO [dbo].[Table_Profile]
FROM sys.tables AS tbl		
LEFT JOIN (SELECT object_id, SUM(reserved_page_count)*8.0/1024 AS "Size (MB)" FROM sys.dm_db_partition_stats GROUP BY object_id) D ON D.object_id=tbl.object_id
LEFT JOIN (SELECT PARENT_OBJECT_ID, COUNT(*) AS Parents FROM SYS.FOREIGN_KEY_COLUMNS GROUP BY PARENT_OBJECT_ID) A ON A.PARENT_OBJECT_ID=tbl.object_id
LEFT JOIN (SELECT REFERENCED_OBJECT_ID, COUNT(*) AS Children FROM SYS.FOREIGN_KEY_COLUMNS GROUP BY REFERENCED_OBJECT_ID) C ON C.REFERENCED_OBJECT_ID=tbl.object_id
LEFT JOIN (		
	SELECT OBJECT_ID	
	, SUM(user_seeks) index_seeks, SUM(user_scans) index_scans, SUM(user_lookups) index_lookups, MAX(user_updates) index_updates	
	, MAX(last_user_update) last_user_update	
	, MAX(last_user_index_read) last_user_index_read	
	FROM (	
		SELECT OBJECT_ID, user_seeks, user_scans, user_lookups, user_updates, last_user_update
		, (SELECT MAX(LastUpdateDate) FROM (VALUES (last_user_seek),(last_user_scan),(last_user_lookup)) AS last_user_read(LastUpdateDate)) last_user_index_read
		FROM sys.dm_db_index_usage_stats
		) L
	GROUP BY OBJECT_ID	
	) L	
ON L.object_id = tbl.object_id		
LEFT JOIN sys.extended_properties AS p ON p.major_id=tbl.object_id AND p.minor_id=0 AND p.class=1
--WHERE SCHEMA_NAME(tbl.schema_id) = 'Financials' --Filter if needed
ORDER BY 1,2,3