SqlServer性能优化之获取表的数据行数,数据大小,索引大小等

系统视图"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界面如下：