系統(tǒng)視圖"sys.tables"相關(guān)信息
相關(guān)SQL如下
USE AdventureWorks2014GO SELECT object_id, index_id, type Into #indexes From sys.indexes;Select object_id, index_id, partition_id Into #parts From sys.partitions;Select object_id, index_id, row_count, partition_id Into #partStats From sys.dm_db_partition_stats;Select t.object_id Id,? ? ? s.name SchemaName,? ? ? t.name TableName,? ? ? t.create_date CreationDate,? ? ? t.modify_date LastModifiedDate,? ? ? Count(Distinct i.index_id) IndexCount,? ? ? Max(ddps.row_count) [RowCount],? ? ? Count(Distinct (Case When i.type In (0,1,5) Then p.partition_id Else Null End)) PartitionCount,? ? ? Sum(Case When i.type In (0,1,5) Then a.total_pages Else0End) *8DataTotalSpaceKB,? ? ? Sum(Case When i.type Not In (0,1,5) Then a.total_pages Else0End) *8IndexTotalSpaceKB,? ? ? Sum(a.used_pages) *8UsedSpaceKB,? ? ? Sum(a.total_pages) *8TotalSpaceKB,? ? ? (Case Max(i.type) When0Then0Else1End)asTableType? From sys.tables t? ? ? Join sys.schemas s? ? ? ? On t.schema_id = s.schema_id? ? ? Join #indexes i? ? ? ? ? On t.object_id = i.object_id? ? ? Join #parts p? ? ? ? ? On i.object_id = p.object_id? ? ? ? ? And i.index_id = p.index_id? ? ? Join (Select container_id,? ? ? ? ? ? ? ? ? ? Sum(used_pages) used_pages,? ? ? ? ? ? ? ? ? ? Sum(total_pages) total_pages? ? ? ? ? ? ? From sys.allocation_units? ? ? ? ? Group By container_id) a? ? ? ? On p.partition_id = a.container_id? ? ? Left Join #partStats ddps? ? ? ? On i.object_id = ddps.object_id? ? ? ? And i.index_id = ddps.index_id? ? ? ? And i.type In (0,1,5) -- Heap, Clustered, Clustered Columnstore? ? ? ? ? ? ? And p.partition_id = ddps.partition_id? Where t.is_ms_shipped =0And i.object_id >255Group By t.object_id, t.Name, t.create_date, t.modify_date, s.name;Drop Table #indexes;Drop Table #parts;Drop Table #partStats;
