遍历查询一个数据库中所有表，列出表名和每个表中的数据行数

 

use [Test]

declare @Tablename nvarchar(50)

declare  @TablenameList table
 (     
   Tablename nvarchar(50), 
   LineNumber bigint
 )
declare cursor_results cursor for
select name from sysobjects where  xtype='U'
open cursor_results
declare @SQLstring nvarchar(100)
declare @AllSQLstring nvarchar(100)
declare @num bigint

fetch next from cursor_results into @Tablename

while @@fetch_status=0           --判断是否成功获取数据
begin
set @AllSQLstring=N'select @a=count(*) from '+@Tablename
exec sp_executesql @AllSQLstring,N'@a bigint output',@num output

insert into @TablenameList values(@Tablename,@num)
print @SQLstring

fetch next from cursor_results into @Tablename
end
select * from @TablenameList
close cursor_results