Data purge on Foreign key connected tables

by Posted on

There was a requirement to delete data from a lot of tables connected with foreign keys without disabling the constrain or using CASCADE feature. Hence here is a simple technique applicable for smaller tables. For large tables, data has to be purged in batches. I am still working on that part at the moment. I used AdventureWorks sample database for this project.

Note: If you are in a hurry to copy-paste and execute the script, scroll down to the bottom of this post and copy the final script. All other scripts above are only sections of the code to help you understand the functioning of the final script and explains how I built the final script.

Below join will fetch all child table details

-- childTableSchema, childTable, childKeyName, childColumnName, parentTable

SELECT
	schema_name(fk.schema_id) childTableSchema,
	object_name(fk.parent_object_id) childTable,
	fk.name childKeyName,
	cl.name childColumnName,
	parent.name parentTable,
	(select name from sys.columns where column_id = fkc.constraint_column_id and object_id = fkc.referenced_object_id) as parentColumnName
FROM
	sys.tables parent
	join
	sys.foreign_keys fk
	on fk.referenced_object_id = parent.object_id
	join sys.foreign_key_columns fkc
	on fkc.parent_object_id = fk.parent_object_id
	and fk.object_id = fkc.constraint_object_id
	join sys.columns cl
	on cl.object_id = fk.parent_object_id
	and cl.column_id = fkc.parent_column_id
	and fkc.referenced_object_id = parent.object_id
	where parent.name = 'Address'

Above join is then passed through a loop to iterate and populate a dependency tree in a table.

set nocount on
IF OBJECT_ID('tempdb.dbo.#fkeys') IS NOT NULL
begin
  drop table #fkeys
end
create table #fkeys (id int identity(1,1),
    childTableSchema sysname,
    childTable sysname,
    childKeyName sysname,
    childColumnName sysname,
    parentTableSchema sysname,
    parentTable sysname,
    parentColumnName sysname default NULL NULL,
	tLevel int default 0,
    done bit default 0)
declare @pTableSchema sysname, @pTableName sysname,
    @pTableColumnName sysname,
    @pTableKeyName sysname, @tLevel int = 0
set @pTableSchema = 'dbo'
set @pTableName = 'entity'
insert into #fkeys(childTableSchema, childTable, childKeyName, childColumnName, parentTableSchema, parentTable)
select @pTableSchema, @pTableName, ind.name, cl.name, 0, 0 from sys.indexes ind join sys.index_columns icl on ind.object_id = icl.object_id
and ind.index_id = icl.index_id
join sys.columns cl on icl.column_id = cl.column_id and cl.object_id = icl.object_id
where is_primary_key = 1 and ind.object_id = object_id(@pTableSchema+'.'+@pTableName)

--select * from #fkeys
declare @pTableId int
while ((select count(1) from #fkeys where done = 0)>0)
begin
  select top 1 @pTableId = id, @pTableSchema= childTableSchema, @pTableName = childTable, @pTableKeyName = childKeyName
  , @pTableColumnName = childColumnName, @tLevel = tLevel
  from #fkeys where done = 0

  insert into #fkeys (childTableSchema, childTable, childKeyName, childColumnName, parentTableSchema, parentTable, parentColumnName, tLevel)
  -- childTableSchema, childTable, childKeyName, childColumnName, parentTable
  SELECT
    schema_name(fk.schema_id) childTableSchema,
    object_name(fk.parent_object_id) childTable,
    fk.name childKeyName,
    cl.name childColumnName,
    @pTableSchema parentTableSchema,
    @pTableName parentTable,
    (select name from sys.columns where column_id = fkc.constraint_column_id and object_id = fkc.referenced_object_id) as parentColumnName,
	(@tLevel+1) currentLevel
  FROM
    sys.tables parent
    join
    sys.foreign_keys fk
    on fk.referenced_object_id = parent.object_id
    join sys.foreign_key_columns fkc
    on fkc.parent_object_id = fk.parent_object_id
    and fk.object_id = fkc.constraint_object_id
    join sys.columns cl
    on cl.object_id = fk.parent_object_id
    and cl.column_id = fkc.parent_column_id
    and fkc.referenced_object_id = parent.object_id
    where parent.name = @pTableName
  
  update #fkeys set done = 1 where childTableSchema = @pTableSchema and childTable = @pTableName
end

select * from #fkeys

set nocount off

Data in the tree is then used to generate delete statements that could be executed in the opposite order without any issues.

set nocount on
IF OBJECT_ID('tempdb.dbo.#fkeys') IS NOT NULL
begin
  drop table #fkeys
end
create table #fkeys (id int identity(1,1),
    childTableSchema sysname,
    childTable sysname,
    childKeyName sysname,
    childColumnName sysname,
    parentTableSchema sysname,
    parentTable sysname,
    parentColumnName sysname default NULL NULL,
	tLevel int default 0,
    done bit default 0)
declare @pTableSchema sysname, @pTableName sysname,
    @pTableColumnName sysname,
    @pTableKeyName sysname, @tLevel int = 0
set @pTableSchema = 'dbo'
set @pTableName = 'entity'
insert into #fkeys(childTableSchema, childTable, childKeyName, childColumnName, parentTableSchema, parentTable)
select @pTableSchema, @pTableName, ind.name, cl.name, 0, 0 from sys.indexes ind join sys.index_columns icl on ind.object_id = icl.object_id
and ind.index_id = icl.index_id
join sys.columns cl on icl.column_id = cl.column_id and cl.object_id = icl.object_id
where is_primary_key = 1 and ind.object_id = object_id(@pTableSchema+'.'+@pTableName)

--select * from #fkeys
declare @pTableId int
while ((select count(1) from #fkeys where done = 0)>0)
begin
  select top 1 @pTableId = id, @pTableSchema= childTableSchema, @pTableName = childTable, @pTableKeyName = childKeyName
  , @pTableColumnName = childColumnName, @tLevel = tLevel
  from #fkeys where done = 0

  insert into #fkeys (childTableSchema, childTable, childKeyName, childColumnName, parentTableSchema, parentTable, parentColumnName, tLevel)
  -- childTableSchema, childTable, childKeyName, childColumnName, parentTable
  SELECT
    schema_name(fk.schema_id) childTableSchema,
    object_name(fk.parent_object_id) childTable,
    fk.name childKeyName,
    cl.name childColumnName,
    @pTableSchema parentTableSchema,
    @pTableName parentTable,
    (select name from sys.columns where column_id = fkc.constraint_column_id and object_id = fkc.referenced_object_id) as parentColumnName,
	(@tLevel+1) currentLevel
  FROM
    sys.tables parent
    join
    sys.foreign_keys fk
    on fk.referenced_object_id = parent.object_id
    join sys.foreign_key_columns fkc
    on fkc.parent_object_id = fk.parent_object_id
    and fk.object_id = fkc.constraint_object_id
    join sys.columns cl
    on cl.object_id = fk.parent_object_id
    and cl.column_id = fkc.parent_column_id
    and fkc.referenced_object_id = parent.object_id
    where parent.name = @pTableName
  
  update #fkeys set done = 1 where childTableSchema = @pTableSchema and childTable = @pTableName
end

select * from #fkeys

select 'delete c from '+childTableSchema+'.'+childTable+' c join '+ parentTableSchema+'.'+parentTable
+' p on c.'+childColumnName+'=p.'+parentColumnName
from #fkeys order by id desc
set nocount off

Published by Sherbaz

Technology enthusiast Sherbaz is a Microsoft certified SQL Server Subject Matter Expert. He had fun automating day to day task to achieve management goals. He has extensive hands-on experience in high availability deployments and support with several years of industry experience performing SQL Server installations, configuration, maintenance, backups, performance tuning, Transactional & P2P replication, migration, high availability solutions (Always on, Failover Clustering, Logshipping and Mirroring) and Disaster recovery. Sherbaz also has basic hands-on experience in other DBMSs like MongoDB, Oracle 9i, 10g, Sybase and MySQL. 1 year on Mongodb, Oracle 9i & 10g. Completed M102 & M202 courses in MongoDB. Sherbaz spends his spare time with automation in electronics, embedded system circuits and IOT in his private lab at home. Also, he supports his customers and users at www.SplitExpense.in for issues with app, website or telegram chat bot @SplitExpenseBot https://t.me/SplitExpenseBot. @TheSherBot https://t.me/TheSherBot is another telegram chat bot, partially artificial intelligent, programmed and maintained by him on python and backend Mongodb database. Sherbaz always showed interest learning new technologies and to think out of the box bringing innovation in work. Some of his work is documented @ www.sherbaz.com. He is now busy learning 3D modeling software on his roadmap towards building his next hobby build, A 3D PRINTER. Subscribe to his youtube channel https://www.youtube.com/c/SherbazMohamed/

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.