查看MSSQL数据库用户每个表占用的空间大小

作者：网络转载发布时间：[ 2013/5/6 10:43:17 ] 推荐标签：

　　很显然这个返回结果是错误的。但是它提供了一种思路，修改后的SQL语句如下：

View Code

IF NOT EXISTS ( SELECT *
                FROM    sys.tables
                WHERE   name = 'tablespaceinfo' )
    BEGIN
        CREATE TABLE tablespaceinfo --创建结果存储表
            (
              Table_Name VARCHAR(50) ，
              Rows_Count INT ，
              reserved INT ，
              datainfo INT ，
              index_size INT ，
              unused INT
            )
    END
DELETE FROM tablespaceinfo
--清空数据表

CREATE TABLE #temp --创建结果存储表
    (
      nameinfo VARCHAR(50) ，
      rowsinfo INT ，
      reserved VARCHAR(20) ，
      datainfo VARCHAR(20) ，
      index_size VARCHAR(20) ，
      unused VARCHAR(20)
    )
DECLARE @tablename VARCHAR(255)
--表名称

DECLARE @cmdsql NVARCHAR(500)

DECLARE Info_cursor CURSOR
FOR
    SELECT '[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' AS Table_Name
    FROM    [INFORMATION_SCHEMA].[TABLES]
    WHERE   TABLE_TYPE = 'BASE TABLE'
            AND TABLE_NAME <> 'tablespaceinfo'

OPEN Info_cursor

FETCH NEXT FROM Info_cursor
INTO @tablename

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @cmdsql = 'insert into #temp exec sp_spaceused ''' + @tablename
            + ''''
        EXECUTE sp_executesql @cmdsql
        FETCH NEXT FROM Info_cursor
INTO @tablename
    END

CLOSE Info_cursor
DEALLOCATE Info_cursor
GO

--itlearner注：显示数据库信息
--sp_spaceused @updateusage = 'TRUE'
--itlearner注：显示表信息

UPDATE #temp
SET     reserved = REPLACE(reserved， 'KB'， '') ，
        datainfo = REPLACE(datainfo， 'KB'， '') ，
        index_size = REPLACE(index_size， 'KB'， '') ，
        unused = REPLACE(unused， 'KB'， '')

INSERT INTO dbo.tablespaceinfo
        SELECT nameinfo ，
                CAST(rowsinfo AS INT) ，
                CAST(reserved AS INT) ，
                CAST(datainfo AS INT) ，
                CAST(index_size AS INT) ，
                CAST(unused AS INT)
        FROM    #temp

DROP TABLE #temp
SELECT Table_Name ，
        Rows_Count ，
        CASE WHEN reserved > 1024
             THEN CAST(reserved / 1024 AS VARCHAR(10)) + 'Mb'
             ELSE CAST(reserved AS VARCHAR(10)) + 'KB'
        END AS Data_And_Index_Reserved ，
        CASE WHEN datainfo > 1024
             THEN CAST(datainfo / 1024 AS VARCHAR(10)) + 'Mb'
             ELSE CAST(datainfo AS VARCHAR(10)) + 'KB'
        END AS Used ，
        CASE WHEN Index_size > 1024
             THEN CAST(index_size / 1024 AS VARCHAR(10)) + 'Mb'
             ELSE CAST(index_size AS VARCHAR(10)) + 'KB'
        END AS index_size ，
        CASE WHEN unused > 1024 THEN CAST(unused / 1024 AS VARCHAR(10)) + 'Mb'
             ELSE CAST(unused AS VARCHAR(10)) + 'KB'
        END AS unused
FROM    dbo.tablespaceinfo
ORDER BY reserved DESC