Compare Page Revisions
« Older Revision - Back to Page History - Newer Revision »
Table of Contents [Hide/Show]
Inserting and Deleting Data Deleting Selected 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
declare @RootTableExt nvarchar(1000) = '[dbo].[Person]' ,@RootColumn nvarchar(1000) = 'PersonID' ,@FilterClause nvarchar(1000) = 'in (1,5,3)' ;with MyData as ( select ForeignKey = fk.name --,OnSchema = ots.name --,OnTable = OnTable.name ,OnTableExt = convert(nvarchar(1000), '[' + ots.name + '].[' + OnTable.name + ']') ,OnColumn = OnColumn.name --,AgainstSchema = ats.name --,AgainstTable = AgainstTable.name ,AgainstTableExt = convert(nvarchar(1000), '[' + ats.name + '].[' + AgainstTable.name + ']') ,AgainstColumn = AgainstColumn.name from sys.foreign_keys fk inner join sys.foreign_key_columns fkcols on fk.object_id = fkcols.constraint_object_id inner join sys.objects OnTable on fk.parent_object_id = OnTable.object_id inner join sys.schemas ots on OnTable.schema_id = ots.schema_id inner join sys.objects AgainstTable on fk.referenced_object_id = AgainstTable.object_id inner join sys.schemas ats on AgainstTable.schema_id = ats.schema_id inner join sys.columns OnColumn on fkcols.parent_column_id = OnColumn.column_id and fkcols.parent_object_id = OnColumn.object_id inner join sys.columns AgainstColumn on fkcols.referenced_column_id = AgainstColumn.column_id and fkcols.referenced_object_id = AgainstColumn.object_id where 1=1 AND AgainstTable.TYPE = 'U' AND OnTable.TYPE = 'U' and OnTable.name not like 'sys%' -- ignore self joins; they cause infinite recursion and OnTable.Name <> AgainstTable.Name ) ,MyRecursion as ( -- base case select TableName = OnTableExt ,Lvl = 1 ,WhereClause = CONVERT(nvarchar(4000), ' where [' + @RootColumn + '] ' + @FilterClause) ,ParentTable = CONVERT(nvarchar(1000), null) from MyData where 1=1 and OnTableExt = @RootTableExt -- recursive case union all select TableName = OnTableExt ,Lvl = r.Lvl + 1 ,WhereClause = CONVERT(nvarchar(4000), case r.Lvl when 1 then ' where [' + OnColumn + '] ' + @FilterClause else ' where [' + OnColumn + '] in (select [' + AgainstColumn + '] from ' + AgainstTableExt + ' ' + r.WhereClause + ')' end ) ,ParentTable = AgainstTableExt from MyData d inner join MyRecursion r on d.AgainstTableExt = r.TableName ) ,MySql as ( select Lvl = max(Lvl) ,Sort2 = 30 ,TableName ,ParentTable ,WhereClause ,SqlCode = 'delete from ' + tablename + WhereClause from MyRecursion group by TableName ,WhereClause ,ParentTable ,WhereClause ) select Lvl ,Sort2 ,TableName --,ParentTable --,WhereClause ,SqlCode from MySql union select distinct Lvl ,Sort2 = 10 ,TableName = '' --,ParentTable = '' --,WhereClause = '' ,SqlCode = '' from MySql union select distinct Lvl ,Sort2 = 20 ,TableName = '' --,ParentTable = '' --,WhereClause = '' ,SqlCode = '/*--- Level ' + CONVERT(varchar(10), Lvl) + ' ---*/' from MySql order by Lvl desc ,Sort2 ,TableName 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.