declare @table table (
rows int,
tablename nvarchar(100)
);
declare @sql NVARCHAR(MAX)
declare @rows int;
insert into @table
select ROW_NUMBER() over (order by name),name from sysobjects where xtype = 'u'
select @rows =MAX(rows) from @table
declare @i int
set @i=1
while @i<@rows
begin
declare @tableName nvarchar(100)
select @tableName = tablename from @table WHERE rows = @i
if @i =1
begin
set @sql =N' select '''+@tableName+''' tablaName, count(1) selectCount from '+@tableName;
end
else
begin
set @sql +=N' union select '''+@tableName+''',count(1) from '+@tableName;
end
set @i=@i+1
end
--exec (@sql)
--print @sql
exec('with cte as ('+@sql+')select * from cte where selectCount > 0')