sql server 数据库所有表大小

原创

mob64ca12f770a6 2024-08-27 05:55:34 ©著作权

文章标签 数据库 SQL sed 文章分类 SQL Server 数据库

©著作权归作者所有：来自51CTO博客作者mob64ca12f770a6的原创作品，请联系作者获取转载授权，否则将追究法律责任

如何获取 SQL Server 数据库中所有表的大小

在管理数据库的过程中，了解数据库中各个表的大小是一个非常重要的任务。这能够帮助我们优化存储，识别潜在问题。在本文中，我们将逐步学习如何在 SQL Server 数据库中获取所有表的大小。

整体流程

我们将以下列步骤来获取所有表的大小，并将结果输出到一个表格中。下面是整个过程的表格展示：

步骤	说明	代码示例
第一步	连接到数据库	`USE [YourDatabaseName]`
第二步	查询表的大小	`EXEC sp_spaceused 'TableName'`
第三步	遍历所有表并汇总结果	`SELECT ...`
第四步	输出表格	`SELECT * FROM #Results`
第五步	清理临时表	`DROP TABLE #Results`

第一步：连接到数据库

首先，我们需要连接到想要查询的数据库。你可以通过以下 SQL 语句来连接到指定的数据库：

USE [YourDatabaseName]

说明：将 YourDatabaseName 替换为你想要查询的数据库的名字。

第二步：查询表的大小

为了获取每个表的大小，我们可以使用 SQL Server 的内置存储过程 sp_spaceused。以下是如何获取特定表的大小的代码示例：

EXEC sp_spaceused 'YourTableName'

说明：将 YourTableName 替换为实际的表名。此命令会返回表的当前大小、数据大小和索引大小等信息。

第三步：遍历所有表并汇总结果

为了方便获取所有表的大小，我们需要遍历数据库中的所有表。我们可以使用以下 SQL 代码生成所有表大小的汇总：

-- 创建一个临时表来存储结果
CREATE TABLE #Results (
    TableName NVARCHAR(255),
    Rows INT,
    ReservedSize NVARCHAR(50),
    DataSize NVARCHAR(50),
    IndexSize NVARCHAR(50),
    UnusedSize NVARCHAR(50)
)

-- 获取当前数据库中所有的表
DECLARE @TableName NVARCHAR(255)
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'INSERT INTO #Results EXEC sp_spaceused ''' + @TableName + ''''
    EXEC sp_executesql @SQL
    FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor
DEALLOCATE TableCursor

说明：

第一步，我们创建一个临时表 #Results 用来存储每个表的大小信息。

然后使用游标遍历所有表，调用 sp_spaceused 把各个表的信息插入我们的临时表中。

第四步：输出表格

现在，我们已经将所有表的大小信息存储在临时表中。接下来，我们只需要查询这个临时表并输出结果：

SELECT * FROM #Results

说明：此命令会输出临时表中的所有记录，即所有表的大小信息。

第五步：清理临时表

最后，我们需要清理我们创建的临时表，以免在以后产生不必要的影响。可以使用以下代码：

DROP TABLE #Results

说明：此命令将删除临时表 #Results。

完整代码示例

将以上所有步骤结合起来，以下是完整的 SQL 代码：

USE [YourDatabaseName]

-- 创建临时表来存储结果
CREATE TABLE #Results (
    TableName NVARCHAR(255),
    Rows INT,
    ReservedSize NVARCHAR(50),
    DataSize NVARCHAR(50),
    IndexSize NVARCHAR(50),
    UnusedSize NVARCHAR(50)
)

-- 获取当前数据库中所有的表
DECLARE @TableName NVARCHAR(255)
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'INSERT INTO #Results EXEC sp_spaceused ''' + @TableName + ''''
    EXEC sp_executesql @SQL
    FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor
DEALLOCATE TableCursor

-- 输出结果
SELECT * FROM #Results

-- 清理临时表
DROP TABLE #Results