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
