原 MSSQL查询数据库大小
Tags: 原创MSSQLSQL Server脚本小麦苗常用数据文件数据库大小MSSQL2000真实大小
查询真实大小(非数据文件分配大小)
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 46 47 48 49 50 51 52 53 54 55 | -- 当前数据库真实大小 SELECT 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 ; /***********************************************************/ /***************************** 数据库真实大小 **********************/ /***********************************************************/ DECLARE @database_name VARCHAR(50) DECLARE @SQL_STR VARCHAR(2000) IF OBJECT_ID('tempdb..#TB_DB_SIZES') IS NOT NULL DROP TABLE #TB_DB_SIZES CREATE TABLE #TB_DB_SIZES(db_name nvarchar(200),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_DB_SIZES SELECT '''+@database_name+''', 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 ' -- 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 * from #TB_DB_SIZES order by totalspacemb desc ; |
SQL Server查询数据库文件分配大小
以下脚本可以用于SQL Server 2005版本: