CREATE VIEW Metadata.Tables
AS
SELECT QUOTENAME(s.NAME) + '.' + QUOTENAME(t.name) AS FQN,
s.name AS SchemaName,
t.name AS TableName,
cc.ColCnt,
pc.PKColCnt,
p.[Rows],
p.EmptyPart,
p.PartMaxRows,
p.Compression,
i.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS IndexType,
ds.name AS DataSpace,
ds.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS DataSpaceDesc,
HasIdent,
TriggerCount,
FKRefCount,
FKCount,
HasClustInd,
NonClustIndCnt,
HasPrimaryKey,
HasUniqueKey,
UniqueIndexCnt,
t.lob_data_space_id AS LobDataSpaceId,
dl.name COLLATE SQL_Latin1_General_CP1_CI_AS AS LobDataSpace,
t.filestream_data_space_id AS FilestreamDataSpaceId,
df.name COLLATE SQL_Latin1_General_CP1_CI_AS AS FilestreamData,
t.max_column_id_used AS MaxColumnIdUsed,
t.lock_on_bulk_load AS LockOnBulkLoad,
t.uses_ansi_nulls AS UsesAnsiNulls,
t.is_replicated AS IsReplicated,
t.has_replication_filter AS HasReplicationFilter,
t.is_merge_published AS IsMergePublished,
t.is_sync_tran_subscribed AS IsSyncTranSubscribed,
t.has_unchecked_assembly_data AS HasUncheckedAssemblyData,
t.text_in_row_limit AS TextInRowLimit,
t.large_value_types_out_of_row AS LargeValueTypesOutOfRow,
t.is_tracked_by_cdc AS IsTrackedByCdc,
t.lock_escalation AS LockEscalation,
t.lock_escalation_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS LockEscalationDesc,
t.create_date AS CreateDate,
t.modify_date AS ModifyDate,
t.OBJECT_ID AS ObjectId
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.tables t (NOLOCK) ON s.SCHEMA_ID = t.SCHEMA_ID
LEFT OUTER JOIN sys.data_spaces dl (NOLOCK) ON dl.data_space_id = t.lob_data_space_id
LEFT OUTER JOIN sys.data_spaces df (NOLOCK) ON df.data_space_id = t.filestream_data_space_id
INNER JOIN sys.indexes i (NOLOCK) ON i.OBJECT_ID = t.OBJECT_ID
AND i.index_id <= 1
LEFT OUTER JOIN sys.data_spaces ds (NOLOCK) ON ds.data_space_id = i.data_space_id
CROSS APPLY (SELECT COUNT(*) AS ColCnt,
SUM(CASE WHEN c.is_identity = 1 THEN 1
ELSE 0
END) AS HasIdent
FROM sys.columns c (NOLOCK)
WHERE c.OBJECT_ID = t.OBJECT_ID) cc
CROSS APPLY (SELECT COUNT (*) AS TriggerCount FROM sys.triggers tr (NOLOCK) WHERE tr.parent_id = t.OBJECT_ID) tc
CROSS APPLY (SELECT SUM(CASE WHEN referenced_object_id = ol.OBJECT_ID THEN 1
ELSE 0
END) AS FKRefCount,
SUM(CASE WHEN fk.parent_object_id = ol.OBJECT_ID THEN 1
ELSE 0
END) AS FKCount
FROM sys.foreign_keys fk (NOLOCK)
INNER JOIN sys.tables ol ON referenced_object_id = ol.OBJECT_ID
OR fk.parent_object_id = ol.OBJECT_ID
WHERE ol.OBJECT_ID = t.OBJECT_ID) fks
CROSS APPLY (SELECT SUM(CASE WHEN index_id > 1 THEN 1
ELSE 0
END) AS NonClustIndCnt,
COUNT(DISTINCT CASE WHEN index_id = 1 THEN index_id
END) AS HasClustInd,
COUNT(DISTINCT CASE WHEN is_primary_key = 1 THEN 1
END) HasPrimaryKey,
COUNT(DISTINCT CASE WHEN is_unique_constraint = 1 THEN is_unique_constraint
END) HasUniqueKey,
SUM(CASE WHEN is_unique = 1 THEN 1
ELSE 0
END) UniqueIndexCnt
FROM sys.indexes i (NOLOCK)
WHERE i.OBJECT_ID = t.OBJECT_ID) ix
CROSS APPLY (SELECT COUNT(*) PKColCnt
FROM sys.indexes i (NOLOCK)
INNER JOIN sys.index_columns ic (NOLOCK) ON ic.index_id = i.index_id
AND ic.OBJECT_ID = i.OBJECT_ID
WHERE i.OBJECT_ID = t.OBJECT_ID
AND i.is_primary_key = 1) pc
CROSS APPLY (SELECT COUNT(*) AS PartCnt,
SUM(p.ROWS) AS [Rows],
SUM(CASE WHEN p.ROWS = 0 THEN 1
ELSE 0
END) AS EmptyPart,
MAX(p.ROWS) AS PartMaxRows,
Util.dbo.StringConcat(DISTINCT p.data_compression_desc COLLATE SQL_Latin1_General_CP1_CI_AS, ', ') AS Compression
FROM sys.partitions p (NOLOCK)
WHERE p.OBJECT_ID = i.OBJECT_ID
AND p.index_id = i.index_id) p
WHERE t.is_ms_shipped = 0