Compare Page Revisions
« Older Revision - Back to Page History - Newer Revision »
Table of Contents [Hide/Show]
Inserting and Deleting Data Viewing the Hierarchy Top Down Hierarchy Bottom-Up Hierarchy
DELETE
with Fkeys as ( select distinct OnTable = OnTable.name ,AgainstTable = AgainstTable.name from sysforeignkeys fk inner join sysobjects onTable on fk.fkeyid = onTable.id inner join sysobjects againstTable on fk.rkeyid = againstTable.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 ) ,MyData as ( select OnTable = o.name ,AgainstTable = FKeys.againstTable from sys.objects o left join FKeys on o.name = FKeys.onTable where 1=1 and o.type = 'U' and o.name not like 'sys%' ) ,MyRecursion as ( -- base case select TableName = OnTable ,Lvl = 1 from MyData where 1=1 and AgainstTable is null -- recursive case union all select TableName = OnTable ,Lvl = r.Lvl + 1 from MyData d inner join MyRecursion r on d.AgainstTable = r.TableName ) select Lvl = max(Lvl) ,TableName ,strSql = 'delete from [' + tablename + ']' from MyRecursion group by TableName order by 1 desc ,2 desc
..1
OrderHeader
....2
OrderLine
OrderHeader\OrderLine
......3
PartMaster
OrderHeader\OrderLine\PartMaster
with Fkeys as ( select distinct OnTable = OnTable.name ,AgainstTable = AgainstTable.name from sysforeignkeys fk inner join sysobjects onTable on fk.fkeyid = onTable.id inner join sysobjects againstTable on fk.rkeyid = againstTable.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 ) ,MyData as ( select OnTable = o.name ,AgainstTable = FKeys.againstTable from sys.objects o left join FKeys on o.name = FKeys.onTable where 1=1 and o.type = 'U' and o.name not like 'sys%' ) ,MyRecursion as ( -- base case select TableName = OnTable ,Lvl = 1 ,DepPath = convert(varchar(max), OnTable) from MyData where 1=1 and AgainstTable is null -- recursive case union all select TableName = OnTable ,Lvl = r.Lvl + 1 ,DepPath = convert(varchar(max), r.DepPath + '\' + OnTable) from MyData d inner join MyRecursion r on d.AgainstTable = r.TableName ) select Level = replicate('..', Lvl) + convert(varchar(3), Lvl) ,TableName ,DepPath from MyRecursion order by DepPath
PartMaster\OrderLine
PartMaster\OrderLine\OrderHeader
with Fkeys as ( select distinct OnTable = OnTable.name ,AgainstTable = AgainstTable.name from sysforeignkeys fk inner join sysobjects onTable on fk.fkeyid = onTable.id inner join sysobjects againstTable on fk.rkeyid = againstTable.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 ) ,MyData as ( select OnTable = FKeys.onTable ,AgainstTable = o.name from sys.objects o left join FKeys on o.name = FKeys.againstTable where 1=1 and o.type = 'U' and o.name not like 'sys%' ) ,MyRecursion as ( -- base case select TableName = AgainstTable ,Lvl = 1 ,DepPath = convert(varchar(max), AgainstTable) from MyData where 1=1 and OnTable is null -- recursive case union all select TableName = AgainstTable ,Lvl = r.Lvl + 1 ,DepPath = convert(varchar(max), r.DepPath + '\' + AgainstTable) from MyData d inner join MyRecursion r on d.OnTable = r.TableName ) select Level = replicate('..', Lvl) + convert(varchar(3), Lvl) ,TableName ,DepPath from MyRecursion order by DepPath
ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.