使用sqlserver临时存储过程,统计所有表的记录数。
中间涉及使用动态SQL,执行结果返回参数中。
create procedure #whcount
as
begin
declare @cnt int
declare @sql nvarchar(1000)
declare @v_tablename nvarchar(100)
declare i cursor for (select a.TableName from DialogTable a)
open i
fetch next from i into @v_tablename
while @@FETCH_STATUS=0
begin
print @v_tablename + ';'
set @sql = 'select @cnt=count(1) from '+@v_tablename
--print @sql
exec sp_executesql @sql, N'@cnt int output', @cnt output
print @cnt
print ''
fetch next from i into @v_tablename
end
close i
end
go
exec #whcount