MS SQL

I developed this query to find all the foreign keys that are linked to a primary table that you would like to either delete from or archive. This query is a CTE (Common Table Expression) and contains a few recursive objects to get the needed information. If you use this please use at your own risk. This does look through the sysobjects table and sys.foreign_key_columns view along with sys.computed_columns view to determine if the column is dependent upon.

——————————————————————————————————————————

/*
This script recursively builds queries based on the dependent upon table. Then manual query is run on the source table.
note: this is a great archiving/delete script to remove all dependant data based on tableA but tableB contains FK's to it
*/

select * into backuptableName
from tableA
----------------Recursive query building for dependant tables
/*
to delete replace "select * from " to "delete X" where "X" is your alias like below a, b, c, d

*/
with Fkeys as (

select distinct

OnTable = OnTable.name
, Oncolumn = col_name(fkcolumns.parent_object_id,fkcolumns.parent_column_id)
,AgainstTable = AgainstTable.name
,AgainstColumn = col_name(fkcolumns.referenced_object_id,fkcolumns.referenced_column_id)
--select *
from

sys.foreign_keys fk

inner join sysobjects onTable
on fk.parent_object_id = onTable.id

inner join sysobjects againstTable
on fk.referenced_object_id = againstTable.id
join sys.foreign_key_columns fkcolumns
on fk.object_id = fkcolumns.constraint_object_id
left join sys.computed_columns col
on fk.object_id = col.object_id

where 1=1
AND AgainstTable.TYPE = 'U'
AND OnTable.TYPE = 'U'
-- ignore self joins; they cause an infinite recursion
and OnTable.Name <> AgainstTable.Name
and col.object_id is null
)

,MyData as (

select
OnTable = o.name
,oncolumn = fkeys.Oncolumn
,AgainstTable = FKeys.againstTable
,AgainstColumn = FKeys.AgainstColumn
--select *
from

sys.objects o
left join FKeys
on o.name = FKeys.onTable
left join sys.computed_columns col
on col.object_id = o.object_id

where 1=1
and o.type = 'U'
and o.name not like 'sys%' --and o.name not like '[_]%'
and col.object_id is null
)

--select *
--from mydata
--where againsttable = '@investigatedTableName'
--and AgainstTable is null
--If needed you can run the above to analyze

,MyRecursion as (

-- base case
select
TableName = OnTable
,Lvl = 1
,OnColumn
,againstColumn
,DepPath = convert(varchar(max), AgainstTable)
,col = convert(varchar(max),'select * from '+OnTable+' a where exists (select 1 from backuptableName b where a.'+oncolumn+'=b.'+AgainstColumn+')')
from
MyData
where 1=1
and AgainstTable = 'tableA' --need to update what table this is against

-- recursive case
union all select
TableName = OnTable
,Lvl = r.Lvl + 1
,d.OnColumn
,d.againstColumn
,DepPath = convert(varchar(max), r.DepPath + '_' + AgainstTable)
,col = convert(varchar(max),' select * from '+d.ontable+' c where exists ('+r.col+' and c.'+d.onColumn+'=a.'+d.AgainstColumn+')')
from
MyData d
inner join MyRecursion r
on d.AgainstTable = r.TableName
where lvl = 1 --for level 2's
--cusor through the levels above 2

union all select
TableName = OnTable
,Lvl = r.Lvl + 1
,e.OnColumn
,e.againstColumn
,DepPath = convert(varchar(max), r.DepPath + '_' + AgainstTable)
,col = convert(varchar(max),' select * from '+e.ontable+' d where exists ('+r.col+' and d.'+e.onColumn+'=c.'+e.AgainstColumn+')')
from
MyData e
inner join MyRecursion r
on e.AgainstTable = r.TableName
where lvl >= 2 ---for level 3's and up

)
select
Level = Lvl
,TableName
,DepPath
,''+tablename+' '+col --you can put what ever is needed here like insert into archive or run a delete from
from
MyRecursion

order by
MyRecursion.DepPath desc