• 统计各表记录数和空间占用情况

    • SELECT s.Name AS SchemaName,

         t.NAME AS TableName,  
         SUM(p.rows)/COUNT(distinct a.type) AS RowCounts,  
         CAST(SUM(a.total_pages) as float) * 8 AS TotalSpaceKB,   
         SUM(a.used_pages) * 8 AS UsedSpaceKB,   
         SUM(a.data_pages) * 8 AS DataSpaceKB,  
         CAST((SUM(a.total_pages) - SUM(a.used_pages)) as float)  * 8 AS UnusedSpaceKB,  
         MIN(FILEGROUP_NAME(a.data_space_id)) as FileGroup  

      FROM sys.tables t
      JOIN sys.schemas s
      ON s.schema_id = t.schema_id
      JOIN sys.indexes i
      ON t.OBJECT_ID = i.object_id
      JOIN sys.partitions p
      ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
      JOIN sys.allocation_units a
      ON p.partition_id = a.container_id
      WHERE t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 AND i.index_id < 2
      GROUP BY t.Name, s.Name
      ORDER BY TotalSpaceKB desc

Powered by Typecho)))   ICP:蜀ICP备05009250号