系统视图"sys.tables"相关信息
https://docs.microsoft.com/zh-cn/sql/relational-databases/system-catalog-views/sys-tables-transact-sql
相关SQL如下
USE AdventureWorks2014
GO
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 Else 0 End) * 8 DataTotalSpaceKB,
Sum(Case When i.type Not In (0, 1, 5) Then a.total_pages Else 0 End) * 8 IndexTotalSpaceKB,
Sum(a.used_pages) * 8 UsedSpaceKB,
Sum(a.total_pages) * 8 TotalSpaceKB,
(Case Max(i.type) When 0 Then 0 Else 1 End) as TableType
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 = 0
And i.object_id > 255
Group By t.object_id, t.Name, t.create_date, t.modify_date, s.name;
Drop Table #indexes;
Drop Table #parts;
Drop Table #partStats;结果如下:
Web界面如下：
