SQL SERVER查看数据库应用

sql server查看所有表大小、所占空间

 1 SELECT 　　 db_name() as DbName,
 2     t.NAME AS TableName,
 3     s.Name AS SchemaName,
 4     p.rows AS RowCounts,
 5     SUM(a.total_pages) * 8 AS TotalSpaceKB, 
 6     CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS 总共占用空间MB,
 7     SUM(a.used_pages) * 8 AS UsedSpaceKB, 
 8     CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
 9     (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
10     CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
11 FROM 
12     sys.tables t
13 INNER JOIN      
14     sys.indexes i ON t.OBJECT_ID = i.object_id
15 INNER JOIN 
16     sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
17 INNER JOIN 
18     sys.allocation_units a ON p.partition_id = a.container_id
19 LEFT OUTER JOIN 
20     sys.schemas s ON t.schema_id = s.schema_id
21 WHERE 
22     t.NAME NOT LIKE 'dt%' 
23     AND t.is_ms_shipped = 0
24     AND i.OBJECT_ID > 0
25 GROUP BY 
26     t.Name, s.Name, p.Rows
27 ORDER BY 
28     总共占用空间MB DESC

查看索引大小

 1 --查看索引大小
 2 如果您想要表的每个索引的大小，请使用以下两个查询中的一个：
 3 
 4 SELECT
 5     i.name                  AS IndexName,
 6     SUM(s.used_page_count) * 8   AS IndexSizeKB
 7 FROM sys.dm_db_partition_stats  AS s 
 8 JOIN sys.indexes                AS i
 9 ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
10 WHERE s.[object_id] = object_id('dbo.TableName')
11 GROUP BY i.name
12 ORDER BY i.name
13 ---第2种方法
14 SELECT
15     i.name              AS IndexName,
16     SUM(page_count * 8) AS IndexSizeKB
17 FROM sys.dm_db_index_physical_stats(
18     db_id(), object_id('dbo.TableName'), NULL, NULL, 'DETAILED') AS s
19 JOIN sys.indexes AS i
20 ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
21 GROUP BY i.name
22 ORDER BY i.name
23 结果通常略有不同，但在1%之内。

查看表数据行数

1 SELECT   a.name, b.rows
2 FROM      sysobjects AS a INNER JOIN
3                  sysindexes AS b ON a.id = b.id
4 WHERE   (a.type = 'u') AND (b.indid IN (0, 1))
5 ORDER BY b.rows DESC