Add Missing Tables in `dbo.purge_table` for Automatic Purging of Old Data

[imajaydwivedi]

Add Missing Tables in dbo.purge_table for Automatic Purging of Old Data

declare @_table_name varchar(500);
declare @_sql nvarchar(max);

if OBJECT_ID('tempdb..#tables') is not null drop table #tables;
create table #tables (table_name varchar(500) not null, [rows] bigint not null);

declare cur_tables cursor local forward_only for
	with cte_user_tables as (
		select table_name = s.name+'.'+t.name from sys.tables t join sys.schemas s on s.schema_id = t.schema_id
		where t.is_ms_shipped = 0
	)
	select ut.table_name
	from cte_user_tables ut
	left join dbo.purge_table pt
	on pt.table_name = ut.table_name
	where 1=1
	and not (	ut.table_name like 'dbo.sma_%'
		or	ut.table_name like '%__staging'
		)
	and pt.date_key is null;

open cur_tables;
fetch next from cur_tables into @_table_name;

while @@FETCH_STATUS = 0
begin
	print 'Working on '+quotename(@_table_name)+'..';
	set @_sql = null;

	set @_sql = N'
	select table_name = '''+@_table_name+''', rows = count(*)
	from '+@_table_name+' t
	right join (select [table_name] = '''+@_table_name+''') d
		on 1=1;
	'
	--print @_sql;
	insert #tables (table_name, [rows])
	exec (@_sql);

	fetch next from cur_tables into @_table_name;
end

close cur_tables;
deallocate cur_tables;

select * from #tables order by rows desc;
go