Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)
Delete records in all related tables

By David Jackson, 2008/08/06
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 tab
/*

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
Total article views: 532 | Views in the last 30 days: 1