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

查看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