sql 查各表大小

 

-- 这是一段查看MS SQL Server 2005数据库中各表大小 SQL 代码  

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

 

----Sql 2000

create table tmp (name varchar(50),rows int,reserved varchar(50),

data varchar(50),index_size varchar(50),unused varchar(50))

insert into tmp (name,rows,reserved,

data,index_size,unused) exec sp_msforeachTable @Command1="sp_spaceused '?'"--sp_spaceused 't_vehicle'

select * from tmp order by data desc

drop table tmp