MSSQL Server 遍历数据库中所有表，列出表名和每个表中的数据行数

1. use [数据库名]
   
2. 
   
3. declare @Tablename nvarchar(50)
   
4. 
   
5. declare @TablenameList table
   
6. (
   
7. Tablename nvarchar(50),
   
8. LineNumber bigint
   
9. )
   
10. declare cursor_results cursor for
    
11. select name from sysobjects where xtype='U'
    
12. open cursor_results
    
13. declare @SQLstring nvarchar(100)
    
14. declare @AllSQLstring nvarchar(100)
    
15. declare @num bigint
    
16. 
    
17. fetch next from cursor_results into @Tablename
    
18. 
    
19. while @@fetch_status=0 --判断是否成功获取数据
    
20. begin
    
21. set @AllSQLstring=N'select @a=count(*) from '+@Tablename
    
22. exec sp_executesql @AllSQLstring,N'@a bigint output',@num output
    
23. 
    
24. insert into @TablenameList values(@Tablename,@num)
    
25. print @SQLstring
    
26. 
    
27. fetch next from cursor_results into @Tablename
    
28. end
    
29. select * from @TablenameList
    
30. 
    
31. close cursor_results

复制代码

很快就能查询到。