--把游标的方法贴出来,供参考 set nocount on create table # ( 表名 varchar(50), 行数 int, 保留空间 varchar(20), 数据使用空间 varchar(20), 索引使用空间 varchar(20), 未使用空间 varchar(20) )declare @name varchar(50) set @name=''declare cur_1 cursor for select [name] from sysobjects where xtype='U'open cur_1 fetch next from cur_1 into @name while(@@FETCH_STATUS=0) begin insert into # exec sp_spaceused @name fetch next from cur_1 into @name endselect 表名,行数 from #drop table # close cur_1 deallocate cur_1
xeqtr1982(HaN)你写的看不到TABLE名啊.
declare @sql varchar(8000) set @sql='' select @sql=@sql+' union all select '''+name+''',count(*) from '+name+'' from sysobjects where xtype='U' set @sql=stuff(@sql,1,12,'') exec(@sql)
exec sp_msforeachtable 'select ''?'' as ''表名'',(select count(1) from ?) as ''行数'''
sp_Msforeachtable 'select ''?'' as 表名,count(*) as 行数 from ?'
如果表太多的话,动态语句容易超出。 还是觉得zlp321002(龙卷风2006)的方法比较好。 exec sp_msforeachtable 'select ''?'' as ''表名'',(select count(1) from ?) as ''行数'''
set nocount on
create table #
(
表名 varchar(50),
行数 int,
保留空间 varchar(20),
数据使用空间 varchar(20),
索引使用空间 varchar(20),
未使用空间 varchar(20)
)declare @name varchar(50)
set @name=''declare cur_1 cursor for
select [name] from sysobjects where xtype='U'open cur_1
fetch next from cur_1 into @name
while(@@FETCH_STATUS=0)
begin
insert into # exec sp_spaceused @name
fetch next from cur_1 into @name
endselect 表名,行数 from #drop table #
close cur_1
deallocate cur_1
set @sql=''
select @sql=@sql+' union all select '''+name+''',count(*) from '+name+'' from sysobjects where xtype='U'
set @sql=stuff(@sql,1,12,'')
exec(@sql)
还是觉得zlp321002(龙卷风2006)的方法比较好。
exec sp_msforeachtable 'select ''?'' as ''表名'',(select count(1) from ?) as ''行数'''