SQL server 中计算表占空间大小

caculate.sql

use [table_name]  
go  
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]  

rowsinfo：记录行数

reserved：总占用空间

datainfo：数据占用空间

index_size/unused：索引占用空间

datainfo=rowsinfo*每行记录大概占用空间

reserved=datainfo+index_size+unused