if exists ( select * from dbo.sysobjects where id = object_id(N'[dbo].[TableSpace]') and objectproperty(id, N'IsUserTable') = 1 ) drop table [dbo].[TableSpace] go create table TableSpace ( TableName varchar(20) , RowsCount char(11) , Reserved varchar(18) , Data varchar(18) , Index_size varchar(18) , Unused varchar(18) ) go declare @sql varchar(500) declare @TableName varchar(20) declare mCursor cursor for select name from sysobjects where xtype='U' open mCursor fetch NEXT from mCursor into @TableName while @@fetch_status = 0 begin set @sql = 'insert into TableSpace ' set @sql = @sql + ' exec sp_spaceused ''' + @TableName + ''' ' exec (@sql) fetch NEXT from mCursor into @TableName end close mCursor deallocate mCursor go --显示结果 select TableName,RowsCount from TableSpace |
