declare @tabs TABLE ( id int identity(1,1), tabname sysname)
declare @res TABLE ( tabname sysname, numrows int )
declare @id int, @numrows int, @tabname sysname, @sql nvarchar(4000)
insert into @tabs (tabname)
select name from sysobjects where type = ‘U’ order by 1 asc
select @id = max(id) from @tabs
while @id > 0
begin
select @tabname = tabname from @tabs where id = @id
set @sql = ‘select ”’ + @tabname + ”’ “tabname”, count(1) “numrows” from [‘ + @tabname + ‘]’
insert into @res ( tabname, numrows )
exec sp_executesql @sql
set @id = @id – 1
end
select tabname, numrows from @res where numrows > 0 order by tabname