统计数据库中所有用户表所占空间大小代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】统计数据库中所有用户表所占空间大小

作者: / 发布于2016/4/27/ 856

CREATE TABLE #Data
    (
      name VARCHAR(100) ,
      row VARCHAR(100) ,
      reserved VARCHAR(100) ,
      data VARCHAR(100) ,
      index_size VARCHAR(100) ,
      unused VARCHAR(100)
    );
CREATE TABLE #DataNew
    (
      name VARCHAR(100) ,
      row INT ,
      reserved INT ,
      data INT ,
      index_size INT ,
      unused INT
    )

DECLARE @name VARCHAR(100);

DECLARE cur CURSOR
FOR
    SELECT  name
    FROM    sysobjects
    WHERE   xtype = 'u'
    ORDER BY name

OPEN cur

FETCH NEXT FROM cur INTO @name

WHILE @@fetch_status = 0
    BEGIN     

        INSERT  INTO #data
                EXEC sp_spaceused @name      

        FETCH NEXT FROM cur INTO @name

    END

CLOSE cur

DEALLOCATE cur

INSERT  INTO #dataNew
        SELECT  name ,
                CONVERT(INT, row) AS row ,
                CONVERT(INT, REPLACE(reserved, 'KB', '')) AS reserved ,
                CONVERT(INT, REPLACE(data, 'KB', '')) AS data ,
                CONVERT(INT, REPLACE(index_size, 'KB', '')) AS index_size ,
                CONVERT(INT, REPLACE(unused, 'KB', '')) AS unused
        FROM    #data;

SELECT  *
FROM    #dataNew
ORDER BY data DESC;

DROP TABLE  #Data;

DROP TABLE  #DataNew;

试试其它关键字

　空间大小　　数据库　

同语言下

可能有用的

贡献的其它代码Label