查询SQL SERVER中某个数据库的每个表的数据量和每行记录所占用空间

查询SQL SERVER中某个数据库的每个表的数据量和每行记录所占用空间

一、执行如下SQL语句

  •  
  • SQL 代码   复制
  • 
    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  '[' + s.[name] +']' +'.' + '[' + t.[name] + ']'
        FROM    sys.tables t
        INNER JOIN sys.schemas s ON t.schema_id = s.schema_id 
        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]
    
    		
  • 二、例如查询 SQL SERVER2014 安装的实例数据库 (ReportServer$MSSQLSERVER2014)

    结果如图

    标签: