Table of Contents [Hide/Show]
See Also Inserting and Deleting Data Deleting Selected Data Generating Delete Statements Viewing Data Related to Selected Entities Detecting Circular Dependencies 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) ,@RootColumn nvarchar(1000) ,@FilterClause nvarchar(1000) select @RootTableExt = '[dbo].[Person]' ,@RootColumn = 'PersonID' ,@FilterClause = 'in (1,2)' ;with MyData as ( select ForeignKey = fk.name ,OnTableExt = convert(nvarchar(1000), '[' + ots.name + '].[' + OnTable.name + ']') ,OnColumn = OnColumn.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 Sort0 = 10 ,Lvl = 0 ,Sort2 = 0 ,TableName = '' ,SqlCode = '/*=================================================================================================' union select Sort0 = 11 ,Lvl = 0 ,Sort2 = 0 ,TableName = '' ,SqlCode = ' USAGE NOTE: Uncomment DELETE statements as needed to address errors. ' union select Sort0 = 12 ,Lvl = 0 ,Sort2 = 0 ,TableName = '' ,SqlCode = '=================================================================================================*/' union select Sort0 = 13 ,Lvl = 0 ,Sort2 = 0 ,TableName = '' ,SqlCode = 'begin transaction' union select distinct Sort0 = 20 ,Lvl ,Sort2 = 10 ,TableName = '' ,SqlCode = '' from MySql union select distinct Sort0 = 20 ,Lvl ,Sort2 = 20 ,TableName = '' ,SqlCode = '/*--- Level ' + CONVERT(varchar(10), Lvl) + ' ---*/' from MySql union select Sort0 = 20 ,Lvl ,Sort2 ,TableName ,SqlCode = case when Lvl >= 2 then '--' else '' end + SqlCode from MySql union select Sort0 = 30 ,Lvl = 0 ,Sort2 = 0 ,TableName = '' ,SqlCode = '' union select Sort0 = 31 ,Lvl = 0 ,Sort2 = 0 ,TableName = '' ,SqlCode = 'rollback transaction' order by Sort0 ,Lvl desc ,Sort2 ,TableName desc
declare @RootTableExt nvarchar(1000) ,@RootColumn nvarchar(1000) ,@FilterClause nvarchar(1000) select @RootTableExt = '[dbo].[Condition]' ,@RootColumn = 'ConditionID' ,@FilterClause = '= -1' ;with MyData as ( select ForeignKey = fk.name ,OnTableExt = convert(nvarchar(1000), '[' + ots.name + '].[' + OnTable.name + ']') ,OnColumn = OnColumn.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 = 'select TableName = ''' + TableName + ''', Qty = count(2) from ' + tablename + WhereClause from MyRecursion group by TableName ,WhereClause ,ParentTable ,WhereClause ) select Sort0 = 20 ,Lvl ,Sort2 ,TableName ,SqlCode = case when Lvl > 1 then 'union ' else '' end + SqlCode from MySql union select Sort0 = 31 ,Lvl = 0 ,Sort2 = 0 ,TableName = '' ,SqlCode = 'order by TableName' order by Sort0 ,Lvl
with fk as ( select OnTable = '[' + ots.name + '].[' + OnTable.name + ']' ,AgainstTable = '[' + ats.name + '].[' + AgainstTable.name + ']' from sys.foreign_keys fk 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 ) /*--- 1 Table ---*/ select NodeQty = 1 ,NodePath = a1.OnTable + '>' + a1.AgainstTable from fk a1 where a1.OnTable = a1.AgainstTable /*--- 2 Tables ---*/ union select NodeQty = 2 ,NodePath = a1.OnTable + '>' + a2.OnTable + '>' + a2.AgainstTable from fk a1 left join fk a2 on a1.AgainstTable = a2.OnTable where 1=1 and a1.OnTable <> a1.AgainstTable and a1.OnTable = a2.AgainstTable /*--- 3 Tables ---*/ union select NodeQty = 3 ,NodePath = a1.OnTable + '>' + a2.OnTable + '>' + a3.OnTable + '>' + a3.AgainstTable from fk a1 left join fk a2 on a1.AgainstTable = a2.OnTable left join fk a3 on a2.AgainstTable = a3.OnTable where 1=1 and a1.OnTable <> a1.AgainstTable and a1.OnTable <> a2.AgainstTable and a1.OnTable = a3.AgainstTable /*--- 4 Tables ---*/ union select NodeQty = 4 ,NodePath = a1.OnTable + '>' + a2.OnTable + '>' + a3.OnTable + '>' + a4.OnTable + '>' + a4.AgainstTable from fk a1 left join fk a2 on a1.AgainstTable = a2.OnTable left join fk a3 on a2.AgainstTable = a3.OnTable left join fk a4 on a3.AgainstTable = a4.OnTable where 1=1 and a1.OnTable <> a1.AgainstTable and a1.OnTable <> a2.AgainstTable and a1.OnTable <> a3.AgainstTable and a1.OnTable = a4.AgainstTable /*--- 5 Tables ---*/ union select NodeQty = 5 ,NodePath = a1.OnTable + '>' + a2.OnTable + '>' + a3.OnTable + '>' + a4.OnTable + '>' + a4.AgainstTable from fk a1 left join fk a2 on a1.AgainstTable = a2.OnTable left join fk a3 on a2.AgainstTable = a3.OnTable left join fk a4 on a3.AgainstTable = a4.OnTable left join fk a5 on a4.AgainstTable = a5.OnTable where 1=1 and a1.OnTable <> a1.AgainstTable and a1.OnTable <> a2.AgainstTable and a1.OnTable <> a3.AgainstTable and a1.OnTable <> a4.AgainstTable and a1.OnTable = a5.AgainstTable order by NodeQty, NodePath
..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.