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