IF NOT EXISTS ( SELECT  *
                FROM    dbo.sysobjects
                WHERE   id = OBJECT_ID(N'[dbo].[tablespaceinfo]')
                        AND OBJECTPROPERTY(id, N'IsUserTable') = 1 ) 
    CREATE TABLE tablespaceinfo --创建结果存储表
        (
          nameinfo varchar(50) ,
          rowsinfo int ,
          reserved varchar(20) ,
          datainfo varchar(20) ,
          index_size varchar(20) ,
          unused varchar(20)
        )

DELETE  FROM tablespaceinfo
 --清空数据表

DECLARE @tablename varchar(255)
 --表名称

DECLARE @cmdsql varchar(500)

DECLARE Info_cursor CURSOR FOR
SELECT o.name
FROM dbo.sysobjects o WHERE OBJECTPROPERTY(o.id, N'IsTable') = 1
AND o.name NOT LIKE N'#%%' ORDER BY o.name

OPEN Info_cursor

FETCH NEXT FROM Info_cursor
INTO @tablename

WHILE @@FETCH_STATUS = 0 
    BEGIN

        IF EXISTS ( SELECT  *
                    FROM    dbo.sysobjects
                    WHERE   id = OBJECT_ID(@tablename)
                            AND OBJECTPROPERTY(id, N'IsUserTable') = 1 ) 
            EXECUTE sp_executesql N'insert into tablespaceinfo exec sp_spaceused @tbname',
                N'@tbname varchar(255)', @tbname = @tablename

        FETCH NEXT FROM Info_cursor
INTO @tablename
    END

CLOSE Info_cursor
DEALLOCATE Info_cursor
GO


--itlearner注：显示数据库信息
sp_spaceused @updateusage = 'TRUE' 
--itlearner注：显示表信息
SELECT  *
FROM    tablespaceinfo
ORDER BY CAST(LEFT(LTRIM(RTRIM(reserved)), LEN(LTRIM(RTRIM(reserved))) - 2) AS int) DESC