计算数据库中各个表的数据量和每行记录所占用空间_dm数据库每条数据记录容量怎么算-CSDN博客

本文链接：https://blog.csdn.net/a497785609/article/details/47395527

本文介绍如何通过SQL查询优化来减少数据库空间占用，并详细解释了如何利用系统存储过程和自定义表来获取各表的详细空间使用情况，包括记录数、占用空间、数据大小、索引大小和未使用空间等信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE TABLE _tmp
    (
      NAME VARCHAR(50) ,
      ROWS INT ,
      reserved VARCHAR(50) ,
      DATA VARCHAR(50) ,
      index_size VARCHAR(50) ,
      unused VARCHAR(50)
    );
INSERT  INTO _tmp
        ( NAME ,
          ROWS ,
          reserved ,
          DATA ,
          index_size ,
          unused
        )
        EXEC sp_MSforeachtable @command1 = "sp_spaceused '?'";
SELECT  NAME AS '表名' ,
        [ROWS] AS '记录数' ,
        reserved AS '占用空间' ,
        DATA AS '数据大小' ,
        index_size AS '索引大小' ,
        unused AS '未使用空间'
FROM    _tmp
WHERE   NAME <> '_tmp'
ORDER BY ROWS DESC
       
DROP TABLE _tmp

或者

CREATE TABLE #tablespaceinfo
    (
      nameinfo VARCHAR(500) ,
      rowsinfo BIGINT ,
      reserved VARCHAR(20) ,
      datainfo VARCHAR(20) ,
      index_size VARCHAR(20) ,
      unused VARCHAR(20)
    )  
 
DECLARE @tablename VARCHAR(255);  
 
DECLARE Info_cursor CURSOR
FOR
    SELECT  '[' + [name] + ']'
    FROM    sys.tables
    WHERE   type = 'U';  
 
OPEN Info_cursor  
FETCH NEXT FROM Info_cursor INTO @tablename  
 
WHILE @@FETCH_STATUS = 0
    BEGIN 
        INSERT  INTO #tablespaceinfo
                EXEC sp_spaceused @tablename  
        FETCH NEXT FROM Info_cursor  
    INTO @tablename  
    END 
 
CLOSE Info_cursor  
DEALLOCATE Info_cursor  
 
--创建临时表
CREATE TABLE [#tmptb]
    (
      TableName VARCHAR(50) ,
      DataInfo BIGINT ,
      RowsInfo BIGINT ,
      Spaceperrow  AS ( CASE RowsInfo
                         WHEN 0 THEN 0
                         ELSE CAST(DataInfo AS decimal(18,2))/CAST(RowsInfo AS decimal(18,2))
                       END ) PERSISTED
    )

--插入数据到临时表
INSERT  INTO [#tmptb]
        ( [TableName] ,
          [DataInfo] ,
          [RowsInfo]
        )
        SELECT  [nameinfo] ,
                CAST(REPLACE([datainfo], 'KB', '') AS BIGINT) AS 'datainfo' ,
                [rowsinfo]
        FROM    #tablespaceinfo
        ORDER BY CAST(REPLACE(reserved, 'KB', '') AS INT) DESC  


--汇总记录
SELECT  [tbspinfo].* ,
        [tmptb].[Spaceperrow] AS '每行记录大概占用空间（KB）'
FROM    [#tablespaceinfo] AS tbspinfo ,
        [#tmptb] AS tmptb
WHERE   [tbspinfo].[nameinfo] = [tmptb].[TableName]
ORDER BY CAST(REPLACE([tbspinfo].[reserved], 'KB', '') AS INT) DESC  

DROP TABLE [#tablespaceinfo]
DROP TABLE [#tmptb]

转自：http://www.cnblogs.com/lyhabc/p/3828496.html