Delete records in all related tables

/*

Used to generate Delete scripts. It does NOT run them. 
You need to copy the output of this script to a new window and run it manually.
Run in text mode. 

Use at own risk! 

Set the @table parameter to the parent table you want to delete from. 
Change the @type parameter to either S or D to generate a select or delete query.
The @debug flag is not useful for 'production' use, but it helps you see what's going on if set to 1

As it stands, this script will generate 4 Select queries in the pubs db, 'seeded' with the titles table.

Dave Jackson
http://glossopian.co.uk

31 July 2008

*/
Set Nocount On

Declare @table Varchar(200)
 ,@lvl Int
 ,@where Varchar(2000)
 ,@type Char(1)
 ,@current Varchar(200)
 
--Set a debug flag 1 = True, 0 = False
--if set to true, then select from work tables, show where clause etc.
Declare @debug Int
Set @debug = 0
 
--Set the table we ultimately want to delete from, and the starting level to one
Select @table = 'titles'
 ,@lvl = 1
 
--Choose type of Query to generate
--S = Select, D = Delete
Select @type = Upper('s')
 
--Set the where clause
--leave @where blank ('') if you want to get rid of all records...(be careful people).
--use double single quotes if you need a single one, or use the pipe symbol, '|', 
--without the quotes instead of a 2 single quotes. 
--The next replace statement replaces them with escaped single quote marks
--making the where clause easier to read.
--Note you must qualify the column names using 2 part naming convention.
Select @where = 'where titles.type like |%cook%|'
 
Select @where = Replace(@where,'|','''')
 
If @debug = 1
 --show what we've got
 Select @where
 
--copy the initial table setting for use later
Select @current = @table
 
--drop object if it already exists
If Object_id('tempdb..#hierarchy') Is Not Null
 Drop Table #hierarchy
 
--create work table
Create Table #hierarchy
 (parent_table varchar(200) not null
 ,parent_column varchar(200) not null
 ,child_table varchar(200)
 ,child_column varchar(200)
 ,lvl int not null
 ,CONSTRAINT UIX_parentchild2 
 UNIQUE NONCLUSTERED (parent_table, child_table, parent_column, child_column)
)
Create Clustered Index Cix_parent On #hierarchy (Parent_table)

--drop object if it already exists
If Object_id('tempdb..#done') Is Not Null
 Drop Table #done
 
--create work done table
Create Table #done (
 Tab Varchar(200))

--drop object if it already exists
If Object_id('tempdb..#FkeyDefs') Is Not Null
 Drop Table #fkeydefs
 
--setup working set - we do this once into a working table 
--because it is slow doing this once each iteration of the following loop
--Taken from John Liu's very good script, find it at
--http://www.sqlservercentral.com/scripts/Miscellaneous/61481/
Select 'PK_Table_Name' = Ccu.Table_name
 ,'PK_Column_Name' = Ccu.Column_name
 ,'FK_Table_Name' = Ccu1.Table_name
 ,'FK_Column_Name' = Ccu1.Column_name
Into #fkeydefs
From Information_schema.Constraint_column_usage Ccu
 Inner Join Information_schema.Referential_constraints Rc
 On Ccu.Constraint_name = Rc.Unique_constraint_name
 Inner Join Information_schema.Constraint_column_usage Ccu1
 On Rc.Constraint_name = Ccu1.Constraint_name
Where Ccu.Constraint_name Not In (Select Constraint_name
 From Information_schema.Referential_constraints)
 
Create Clustered Index Cix_pk_table_name2 On #fkeydefs (
 Pk_table_name)

If @debug = 1
 --show what we've got
 Select *
 From #fkeydefs

declare @errs int, @rows int
 
--Infinite loop ;0)
While 1 = 1
 Begin
 Insert #hierarchy
 Select Pk_table_name
 ,Pk_column_name
 ,Fk_table_name
 ,Fk_column_name
 ,@lvl
 From #fkeydefs
 Where Pk_table_name Like @current
 and not Exists(Select H1.Parent_table
 From #hierarchy H1
 Left Join #hierarchy H2
 On H1.Child_table = H2.Parent_table
 And H1.Parent_table = H2.Parent_table
 Where H2.Child_table Is Null 
 And H1.Parent_table = @current)
 select @errs = @@Error, @rows = @@rowcount 

 if @rows = 0
 --This looks to see if there are any more to do
 Begin
 If Not Exists (Select H1.Parent_table
 From #hierarchy H1
 Left Join #hierarchy H2
 On H1.Child_table = H2.Parent_table
 And H1.Parent_table = H2.Parent_table
 Where H2.Child_table Is Null 
 And H1.Child_table Not In (Select Tab
 From #done))
 --There isn't, so quit this loop
 Break
 End
 
 --Keep a record of what we have done, or we'll keep reprocessing when we are at the 
 --last leaf of the tree.
 Insert #done
 Select @current
 
 Set @lvl = @lvl + 1
 
 --Reset the name of the table we are after
 Select @current = H1.Child_table
 From #hierarchy H1
 Left Join #hierarchy H2
 On H1.Child_table = H2.Parent_table
 And H1.Parent_table = H2.Parent_table
 Where H2.Child_table Is Null 
 And H1.Child_table Not In (Select Tab
 From #done)
 
 --Bottom of the While loop, jump back to the top here
 End
 
If @debug = 1
 --show what we've got
 Select *
 From #hierarchy
 Order By Lvl Desc
 
Set Nocount On 

--work out the hierarchy
--taken from the example in BOL (2000)
Declare @line Varchar(255)
 
If Object_id('tempdb..#stack') Is Not Null
 Drop Table #stack

Create Table #stack (Item Char(255), Lvl Int)

If Object_id('tempdb..#stack2') Is Not Null
 Drop Table #stack2

Create Table #stack2 (Item Char(255), Lvl Int)

Insert Into #stack
Values (@table, 1)

Select @lvl = 1

While @lvl > 0
 Begin
 If Exists (Select *
 From #stack
 Where Lvl = @lvl)
 Begin
 Select @table = Item
 From #stack
 Where Lvl = @lvl
 
 --uncomment the next line if you don't want to see the hierarchy unless you are in debug mode...
 --If @debug = 1
 Begin
 Select @line = '--' + Space((@lvl * 4) - 1) + @table
 Print @line
 End
 
 Select @line = @table
 
 If Not Exists (Select 1
 From #stack2
 Where Item = @line)
 Insert Into #stack2
 Select @line
 ,@lvl
 
 Delete From #stack
 Where Lvl = @lvl
 And Item = @table
 
 Insert #stack
 Select Child_table
 ,@lvl + 1
 From #hierarchy
 Where Parent_table = @table
 
 If @@rowcount > 0
 Select @lvl = @lvl + 1
 End
 Else
 Select @lvl = @lvl - 1
 End --Of the WHILE Loop
 
If @debug = 1
 --show what we've got
 Begin
 Select Item
 ,Lvl
 From #stack2
 Order By Lvl Desc
 ,Item Desc
 End
Print '--========================================================='
-- =============================================
-- Declare a READ_ONLY cursor, 
-- funnily enough, the quickest part of this script...
-- =============================================
Print 'Begin Tran
-- rollback
-- commit
-- Note: If there is no FK defined, this script does NOT pick it up.'

Declare BuildSelectDeleteQuery Cursor Read_only For
Select Item
 ,Max(Lvl)
From #stack2
Group By Item
Order By Max(Lvl) Desc
 
Declare @item Varchar(255)
 ,@lvl2 Int
 ,@sql Varchar(8000)
 ,@copysql Varchar(8000)
 ,@joinsql Varchar(8000)

Open BuildSelectDeleteQuery

Fetch Next From BuildSelectDeleteQuery
Into @item,
 @lvl

While (@@fetch_status <> -1)
 Begin
 If (@@fetch_status <> -2)
 Begin
 Select @item = Rtrim(@item)

 Select @lvl2 = @lvl
 
 --Don't muck about with the line breaks, 
 --they are essential for formatting the output
 If @type = 'S'
 Select @sql = Char(13) + 'Select ' + @item + '.* '
 
 If @type = 'D'
 Select @sql = Char(13) + 'Delete ' + @item
 
 Select @sql = @sql + ' from' + Char(13) + ' ' + @item
 Select @joinsql = ''

 While @lvl2 <> 0
 Begin
 Select @copysql = @sql
 Select @sql = @sql + Char(13) + ' inner join ' + Parent_table + Char(13) +
 ' on ' + Parent_table + '.' + Parent_column + ' = ' + Child_table + '.' + Child_column
 From #stack2 S
 Inner Join #hierarchy H
 On S.Item = H.Child_table
 Where H.Child_table = @item
 
 if @@rowcount > 1
 --found more than one, so we need to re-construct the join condition
 --This assumes the compound column keys used in the join are named the same...
 --If they are not, manual intervention will be required
 Begin
 Select top 1 @joinsql = @joinsql + Char(13) + ' inner join ' + Parent_table + ' on ' 
 + Parent_table + '.' + Parent_column + ' = ' + Child_table + '.' + Child_column
 From #stack2 S
 Inner Join #hierarchy H
 On S.Item = H.Child_table
 Where H.Child_table = @item 

 Select @joinsql = @joinsql + Char(13) + ' and '
 + Parent_table + '.' + Parent_column + ' = ' + Child_table + '.' + Child_column
 From #stack2 S
 Inner Join #hierarchy H
 On S.Item = H.Child_table
 Where H.Child_table = @item and H.child_column = H.parent_column
 And patindex('%' + H.child_column + '%', @joinsql) = 0
 
 Select @sql = @copysql + ' ' + @joinSQL
 Select @joinsql = ''
 End

 Select @item = Parent_table
 From #stack2 S
 Inner Join #hierarchy H
 On S.Item = H.Child_table
 Where S.Item = @item
 
 Select @lvl2 = @lvl2 - 1
 End --End of While @lvl2 <> 0

 Print @sql + Char(13) + @where
 End --End of (@@fetch_status <> -2) While
 
 Fetch Next From BuildSelectDeleteQuery
 Into @item, @lvl

 End --End of (@@fetch_status <> -1) While
 
Close BuildSelectDeleteQuery

Deallocate BuildSelectDeleteQuery

Set Nocount Off