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

最新推荐文章于 2024-07-05 17:12:03 发布

码农小宇哥

最新推荐文章于 2024-07-05 17:12:03 发布

阅读量2k

点赞数

文章标签： SQL 数据库统计表遍历记录数动态SQL

原文链接：https://blog.csdn.net/happystarcat/article/details/7324007

版权

use [数据库名]

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