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 &lt;&gt; 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 &gt;= 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