Sql Server2000/2005 查询所有表的大小和空间使用信息

sql Server2000：

SELECT  OBJECT_NAME(id) tablename ,
        8 * reserved / 1024 reserved ,
        RTRIM(8 * dpages / 1024) + 'Mb' used ,
        8 * ( reserved - dpages ) / 1024 unused ,
        8 * dpages / 1024 - rows / 1024 * minlen / 1024 free ,
        rows ,
        *
FROM    sysindexes
--where indid=1
ORDER BY reserved DESC

sql Server2005:

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 

数据库运行一段时间，数据增加，有的表大小都过G 的，根据需要，可以适当删除一些垃圾数据信息，另外清理日志，以及收缩数据库，也是处理数据库的必要手段。给数据瘦身吧！