group_results_same_tables.sql

/*
	Deze query zorgt voor een verzameling van alle _LOG_ tabellen en doet een group by op aantal foutmeldingen op aflopende volgorde
*/

declare @allthesqlwithunions nvarchar(max) = ''
declare @selectsql nvarchar(max)
declare @sqlstatement nvarchar(max)

set @sqlstatement = '
	declare log_cursor cursor for 
	select
		''
			select 
				aantalrecords = 1
				,errors = case when message is not null then 1 else 0 end
				, ''''''+o.name + '''''' as _LOG_table 
				,entityname
				,'''' select * from '' + o.name + '' where entityname = "'''' + entityname + ''''" '''' as SQL
			from '' + o.name + ''
			union all 
		'' 
	from sys.objects o 
	where 
		o.type_desc = ''user_table''
		and 
		name like ''_log_%''
 '
exec sp_executesql @sqlstatement


open log_cursor
fetch next from log_cursor
into @selectsql

while @@fetch_status = 0
begin

	set @allthesqlwithunions = @allthesqlwithunions + @selectsql 
 
	fetch next from log_cursor 
	into @selectsql

end
close log_cursor
deallocate log_cursor

set @allthesqlwithunions = substring(@allthesqlwithunions, 0, len(@allthesqlwithunions)-9)
set @allthesqlwithunions = ' 
	select 
		aantalrecords = sum(aantalrecords) 
		,errors = sum(errors)
		,_LOG_table 
		,entityname 
		,SQL
	from ( ' + @allthesqlwithunions + char(13) + ' )  T	group by T.EntityName, T._LOG_table, T.SQL 
'  

exec sp_executesql @allthesqlwithunions
print @allthesqlwithunions
go