sql server 查询数据库表的大小语句

来源:互联网 发布:微分怎么编程 编辑:程序博客网 时间:2024/06/05 19:23
查看MS SQL Server 数据库中各表大小 SQL 代码
2009-05-23 18:12

-- 这是一段查看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

 

另外还有两条语句

exec sp_msforeachtable

 

转自http://hi.baidu.com/djmdai84/blog/item/936cb00080bc73067aec2c77.html

原创粉丝点击