SQL Server 查询数据库每张表的占用空间

最新推荐文章于 2023-08-14 20:10:25 发布

小薯仔

最新推荐文章于 2023-08-14 20:10:25 发布

阅读量309

点赞数

分类专栏： SQL

本文链接：https://blog.csdn.net/xiaopanpan92/article/details/104494257

版权

SQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

具体

查询表占用空间的主要语句为 EXEC sp_spaceused @temp;

微软官方解释:

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-spaceused-transact-sql?view=sql-server-ver15

--判断临时表是否存在，存在则删除重建
IF EXISTS ( SELECT  1
            FROM    tempdb..sysobjects
            WHERE   id = OBJECT_ID('tempdb..#tabName')
                    AND xtype = 'u' )
    DROP TABLE #tabName;
GO
CREATE TABLE #tabName
    (
     tabname VARCHAR(100)
    ,rowsNum VARCHAR(100)
    ,reserved VARCHAR(100)
    ,data VARCHAR(100)
    ,index_size VARCHAR(100)
    ,unused_size VARCHAR(100)
    );
 
DECLARE @name VARCHAR(100);
DECLARE @schemas VARCHAR(100);
DECLARE @temp VARCHAR(100);
DECLARE cur CURSOR
FOR
    SELECT  a.name
           ,s.name
    FROM    sys.objects a
           ,sys.schemas s
    WHERE   a.type = 'u'
            AND a.schema_id = s.schema_id
    ORDER BY a.name;
OPEN cur;
FETCH NEXT FROM cur INTO @name , @schemas;
WHILE @@fetch_status = 0
    BEGIN
        SET @temp = @schemas + '.[' + @name + ']';
        INSERT  INTO #tabName
                EXEC sp_spaceused @temp;
        PRINT @temp;
 
        FETCH NEXT FROM cur INTO @name , @schemas;
    END;
CLOSE cur;
DEALLOCATE cur;

SELECT  tabname AS '表名'
       ,rowsNum AS '表数据行数'
       ,reserved AS '保留大小'
       ,data AS '数据大小'
       ,index_size AS '索引大小'
       ,unused_size AS '未使用大小'
FROM    #tabName
ORDER BY CAST(ISNULL(rowsNum , 0) AS INT) DESC;