合 MSSQL查询前几张大表（查询表大小）
Tags： MSSQLSQL Server脚本大表top N查询
👉 本文共约83个字，系统预计阅读时间或需1分钟。
查询当前库的前10张大表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | SELECT TOP 10 t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts, CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB, CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id GROUP BY t.Name, s.Name, p.Rows ORDER BY SUM(a.total_pages) desc; |
查询所有库的前10张大表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | DECLARE @database_name VARCHAR(50) DECLARE @SQL_STR VARCHAR(2000) IF OBJECT_ID('tempdb..#TB_TOP_SIZES') IS NOT NULL DROP TABLE #TB_TOP_SIZES CREATE TABLE #TB_TOP_SIZES(db_name nvarchar(200),TABLE_SCHEMA nvarchar(200),TABLE_NAME nvarchar(200),tb_rows bigint,TotalSpaceMB NUMERIC(36, 2),UsedSpaceMB NUMERIC(36, 2),UnusedSpaceMB NUMERIC(36, 2)) DECLARE DATEBASE_INFO_CURSOR CURSOR FOR SELECT name FROM sys.databases where state=0 -- where name not in ('master','model','msdb','tempdb') -- and state=0 ORDER BY Name OPEN DATEBASE_INFO_CURSOR FETCH NEXT FROM DATEBASE_INFO_CURSOR INTO @database_name WHILE @@FETCH_STATUS=0 BEGIN SET @SQL_STR='INSERT INTO #TB_TOP_SIZES SELECT TOP 20 '''+@database_name+''', s.Name AS SchemaName, t.NAME AS TableName, p.rows AS RowCounts, CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB, CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB FROM ['+@database_name+'].sys.tables t INNER JOIN ['+@database_name+'].sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN ['+@database_name+'].sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN ['+@database_name+'].sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN ['+@database_name+'].sys.schemas s ON t.schema_id = s.schema_id GROUP BY t.Name, s.Name, p.Rows ORDER BY SUM(a.total_pages) desc' -- print (@SQL_STR) EXEC (@SQL_STR) FETCH NEXT FROM DATEBASE_INFO_CURSOR INTO @database_name END CLOSE DATEBASE_INFO_CURSOR DEALLOCATE DATEBASE_INFO_CURSOR select top 10 * from #TB_TOP_SIZES order by TotalSpaceMB desc ; |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
