SQL 查询表大小

一， SQL2000

     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

二，SQL2005

   DECLARE @tablespaceinfo TABLE (  

    nameinfo varchar(50),  

    rowsinfo int,  

    reserved varchar(20),  

    datainfo varchar(20),  

    index_size varchar(20),  

    unused varchar(20)  

)  

 

DECLARE @tablename varchar(255);  

 

DECLARE Info_cursor CURSOR FOR 

    SELECT [name] FROM sys.tables 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  

 

SELECT * FROM @tablespaceinfo  

    ORDER BY Cast(Replace(reserved,'KB','') as INT) DESC