use [resset2]
declare @Tablename nvarchar(50),@sqlstring nvarchar(100),@ALLSQLstring nvarchar(100),@num bigint
declare @TablenameList table
(
Tablename nvarchar(50),
LineNumber bigint
)
declare CursorResults cursor for
select name from sysobjects where xtype='u'
open CursorResults
fetch next from CursorResults into @Tablename
while @@FETCH_STATUS=0
begin
set @ALLSQLstring=N'select @a=count(1) from '+ @Tablename
exec sp_executesql @ALLSQLstring,N' @a bigint output',@num output
insert into @TablenameList values(@Tablename,@num)
print @sqlstring
fetch next from CursorResults into @Tablename
end
select * from @TablenameList
close CursorResults
deallocate CursorResults
SQL Server 查询数据库中所有表,列出表名和每个表的数量
于 2023-03-23 15:06:45 首次发布
该SQL脚本声明并使用了游标来遍历sysobjects系统表中所有用户定义的表(类型为u),对每个表执行计数查询,计算表中的记录数,并将结果存储到一个新的表格TablenameList中,包含表名和行数。最后,脚本打印查询语句并显示TablenameList的所有内容。
摘要由CSDN通过智能技术生成