Dynamic deletion of records from qualified tables

originally posted 2014-04-22

1	originally posted 2014-04-22

--delete all records that contain a specified value for a given key field...
--... for all tables that have the key field and that conform to a certain name pattern

--concatenate a list of IDs into a delimited string for use in an IN statement
declare @ids nvarchar(max)
declare @sql nvarchar(max)

;with cteIds as
(
  --source table for the key values for the deletion
  select [DistributionCodeId] 
  from [SIP_NPT_Posted]
)

select
@ids = stuff(
(
    select ',''' + cte.[DistributionCodeId] + ''''
    from cteIds cte
    order by cte.[DistributionCodeId]
    for xml path('')
),1,1,'')

--the concatenated IDs 
select @ids

--create a delete script for each table that conforms to the name pattern using the concatenated IDs for the IN statement of the WHERE clause
;with cteTables as
(
  select [sql] = 'delete from [' + t.[name] + '] where [DistributionCodeId] in (' + isnull(@ids,'''''') + ')'
  from sys.tables t
  inner join sys.columns c on t.object_id = c.object_id
  where t.[type] = 'U'
  and t.[name] like 'SIP_NPT_POST%' -- table name pattern
  and c.[name] = 'DistributionCodeId' --column name 
  and t.name &amp;lt;&amp;gt; 'SIP_NPT_Posted' --exclude the [SIP_NPT_Posted] table b/c deletion for it is handled elsewhere.
)

--concatenate the delete scripts into a single string of SQL
select
@sql = stuff(
(
    select ';' + cte.[sql]
    from cteTables cte
    order by cte.[sql]
    for xml path('')
),1,1,'')

select [@sql] = @sql

--execute the SQL
--exec(@sql); --UNCOMMENT ME TO DELETE!

/*

[OUTPUT]

@ids contains this...

'100009-010','100222-010'


@sql contains this...

delete from [SIP_NPT_POST_Benefits] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_Earnings] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_EnDocuments] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_EnSessions] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_EnTransactions] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_FedTaxes_Benefits] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_FedTaxes_Earnings] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_JobPaySchedules] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_JobPriorExpenses] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_JobRemainingPayPeriods] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_JobTotalPayPeriods] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_Payees] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_PaySummary] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_PostPaySchedules] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_PostTotalPayPeriods] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_Projects] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_StateTaxes_Benefits] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_StateTaxes_Earnings] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_WorkersCompensation] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_PostedPayee] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_PostedTran] where [DistributionCodeId] in ('100009-010','100222-010')

*/

--delete all records that contain a specified value for a given key field...

--... for all tables that have the key field and that conform to a certain name pattern

--concatenate a list of IDs into a delimited string for use in an IN statement

declare @ids nvarchar(max)

declare @sql nvarchar(max)

;with cteIds as

(

--source table for the key values for the deletion

select [DistributionCodeId]

from [SIP_NPT_Posted]

)

select

@ids = stuff(

(

select ',''' + cte.[DistributionCodeId] + ''''

from cteIds cte

order by cte.[DistributionCodeId]

for xml path('')

),1,1,'')

--the concatenated IDs

select @ids

--create a delete script for each table that conforms to the name pattern using the concatenated IDs for the IN statement of the WHERE clause

;with cteTables as

(

select [sql] = 'delete from [' + t.[name] + '] where [DistributionCodeId] in (' + isnull(@ids,'''''') + ')'

from sys.tables t

inner join sys.columns c on t.object_id = c.object_id

where t.[type] = 'U'

and t.[name] like 'SIP_NPT_POST%' -- table name pattern

and c.[name] = 'DistributionCodeId' --column name

and t.name &lt;&gt; 'SIP_NPT_Posted' --exclude the [SIP_NPT_Posted] table b/c deletion for it is handled elsewhere.

)

--concatenate the delete scripts into a single string of SQL

select

@sql = stuff(

(

select ';' + cte.[sql]

from cteTables cte

order by cte.[sql]

for xml path('')

),1,1,'')

select [@sql] = @sql

--execute the SQL

--exec(@sql); --UNCOMMENT ME TO DELETE!

[OUTPUT]

@ids contains this...

'100009-010','100222-010'

@sql contains this...

delete from [SIP_NPT_POST_Benefits] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_Earnings] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_EnDocuments] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_EnSessions] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_EnTransactions] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_FedTaxes_Benefits] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_FedTaxes_Earnings] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_JobPaySchedules] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_JobPriorExpenses] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_JobRemainingPayPeriods] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_JobTotalPayPeriods] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_Payees] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_PaySummary] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_PostPaySchedules] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_PostTotalPayPeriods] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_Projects] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_StateTaxes_Benefits] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_StateTaxes_Earnings] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_WorkersCompensation] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_PostedPayee] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_PostedTran] where [DistributionCodeId] in ('100009-010','100222-010')