查看MSSQL数据库每个表占用的空间大小.pdf

在SQL Server中，MSSQL数据库管理是至关重要的任务，特别是在优化存储空间使用时。`sp_spaceused`存储过程是管理员用于检查表或整个数据库空间使用情况的重要工具。这个存储过程提供了关于表的行数、已分配和保留的磁盘空间的信息。下面将详细介绍`sp_spaceused`的用法，并探讨如何一次性查看数据库中所有表的空间大小。 `sp_spaceused`的基本语法如下： ```sql sp_spaceused [[@objname =] 'objname'] [,[@updateusage =] 'updateusage'] ``` - `@objname` 参数是你要查询的表名，如果不提供，则会显示整个数据库的信息。 - `@updateusage` 参数决定是否运行`DBCC UPDATEUSAGE`来更新统计信息。默认设置为 `FALSE`，即不更新。 例如，你可以这样查询名为 `titles` 的表的空间信息： ```sql USE pubs; EXEC sp_spaceused 'titles'; ``` 若想获取整个数据库的最新空间使用情况，可以加上 `@updateusage` 参数： ```sql USE pubs; EXEC sp_spaceused @updateusage = 'TRUE'; ``` 然而，当你需要查看数据库中所有表的空间大小时，可以采用以下三种方法： **方法一** 使用`sp_MSforeachtable`，这是一个扩展存储过程，遍历数据库中的所有表并执行`sp_spaceused`： ```sql exec sp_MSforeachtable "exec sp_spaceused '?'" ``` 这种方法快速但输出可能不太直观。 **方法二** 创建一个临时结果表 `tablespaceinfo`，然后通过游标逐个查询并插入结果： ```sql -- 创建结果存储表 IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[tablespaceinfo]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) CREATE TABLE tablespaceinfo (nameinfo VARCHAR(50), rowsinfo INT, reserved VARCHAR(20), datainfo VARCHAR(20), index_size VARCHAR(20), unused VARCHAR(20)) -- 清空数据表 DELETE FROM tablespaceinfo -- 定义变量 DECLARE @tablename VARCHAR(255) -- 声明游标 DECLARE Info_cursor CURSOR FOR SELECT o.name FROM dbo.sysobjects o WHERE OBJECTPROPERTY(o.id, N'IsTable') = 1 AND o.name NOT LIKE N'#%%' ORDER BY o.name -- 打开并处理游标 OPEN Info_cursor FETCH NEXT FROM Info_cursor INTO @tablename WHILE @@FETCH_STATUS = 0 BEGIN IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(@tablename) AND OBJECTPROPERTY(id, N'IsUserTable') = 1) EXEC sp_executesql N'INSERT INTO tablespaceinfo EXEC sp_spaceused @tbname', N'@tbname VARCHAR(255)', @tbname = @tablename FETCH NEXT FROM Info_cursor INTO @tablename END -- 关闭并释放游标 CLOSE Info_cursor DEALLOCATE Info_cursor -- 显示结果 SELECT * FROM tablespaceinfo ORDER BY CAST(LEFT(LTRIM(RTRIM(reserved)), LEN(LTRIM(RTRIM(reserved))) - 2) AS INT) DESC ``` **方法三** 直接使用 SQL 查询，计算每个表的总大小（单位转换为MB）： ```sql SELECT object_name(id) AS tablename, 8 * reserved / 1024 AS reserved, RTRIM(8 * dpages / 1024) + 'Mb' AS used, 8 * ( CAST(reserved AS INT) - CAST(data AS INT) - CAST(index_size AS INT) ) / 1024 AS unused FROM sys.sysindexes WHERE objectproperty(id, N'IsUserTable') = 1 ORDER BY CAST(reserved AS INT) DESC; ``` 这三种方法都可以帮助你获取MSSQL数据库中所有表的空间占用情况，选择最适合你需求的方法即可。在进行数据库优化时，这些信息非常关键，可以用来识别可能的空间浪费，以便进行表重构、索引优化或数据清理等操作。