Customers come and go. Free trials and specials pull customers too and away from your software offering. Maybe your product is the flavor of the month for a demographic; only to be dropped the following calendar page. Some customers simply just close up shop. Maybe your registration process isn’t quite as intuitive as it should be; confusing customers and prompting them to start over. Whatever the case may be, chances are you have a lot of unused and abandoned data in your database. Like me you may be faced with the monumental task of not just identifying this data but archiving, migrating or just plain removing it. You may not even get the benefit of a downtime to do it.
So aside from batching out the process to smaller chunks to avoid impacting the entire database and ensuring that the data you are removing or archiving is verified for removal or archival, you might find yourself with the task of properly navigating the treacherous FK relationship path in your database. So if I need to delete all entries for a customer in my multi tenant database, I not only have to make sure I do indeed remove all of their data but I also have to make sure my script knows what order to perform the operations in. I cant exactly delete a customer if they have some phone numbers still dependent on the CustomerID can I. Obviously database design for cascading operations can solve this issue. But if your database design did not already include cascading dml then your going to be making an schema change on every FK in your database. If that change doesn’t make you shudder both at the immensity of the task and the massive delta for your QA process to go through then you I have a different set of problems.
So if you find yourself in a similar predicament as myself I made this handy script that not only walks your FK tree and labels the depth of your leaf’s but will construct statements to perform a dml of your choice(with some tweaking). It will even join a child table to the parent it depends on if the child itself is missing an identifier column for your tennant. It will then return your list of queries to perform your desired action in the proper order. Currently it ignores cyclical dependencies and any nodes that are tenant unspecific all the way through their dependencies.
declare @columnname varchar(256)
set @columnname = 'clinicid' --Column used as multi tennant identifier
declare @TblInfo table(
FK_Table varchar(max),
FK_Column varchar(max),
PK_Table varchar(max),
PK_Column varchar(max),
Constraint_Name varchar(max),
columnPresent bit,
columnPresentPK bit,
depth int
)
Declare @FKRelationshipJoins table (
FK_table Varchar(max)
,PK_table Varchar(max)
,PK_Column Varchar(max)
,FK_Column Varchar(max)
,ColumnPresent Varchar(max)
,ColumnPresentPK Varchar(max)
,FullTBlPath Varchar(max)
,FullColPath varchar(max)
,OriginalPath Varchar(max)
,depth Varchar(max)
,JoinStmt Varchar(max)
)
declare @Contstraints table (
FK_Table varchar(max),
FK_Column varchar(max),
PK_Table varchar(max),
PK_Column varchar(max),
Constraint_Name varchar(max)
)
--Collect all FK relationships for database
insert @Contstraints
SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
order by FK.TABLE_NAME
--Cte for tracking labeling the leaf position of each table in the fk relationship tree
;
with cte as (
SELECT
FK_Table,
FK_Column,
PK_Table,
PK_Column,
Constraint_Name,
1 as depth
FROM
@Contstraints q1
where q1.FK_Table !=q1.PK_Table
union all
SELECT
q2.FK_Table,
q2.FK_Column,
q2.PK_Table,
q2.PK_Column,
q2.Constraint_Name,
depth+1 depth
FROM @Contstraints q2
join cte on cte.FK_Table = q2.PK_Table
where q2.FK_Table !=q2.PK_Table
)
--Save results for Relationships and specify whether table has a Multitennant identifier built in
insert @TblInfo
Select distinct
cte.FK_Table --Table with FK dependency
,FK_Column --Column that contains the FK linked Identifier
,PK_Table --Table Fk relatioship is dependent on
,PK_Column --Column that dependent fk relatioship references
,Constraint_Name, --Name of constraint
(select COUNT(*) from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = cte.FK_Table and COLUMN_NAME = @columnname),
(select COUNT(*) from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = cte.PK_Table and COLUMN_NAME = @columnname),
cte.depth
from (
select max(depth)Depth,FK_Table from cte group by FK_Table) q1
join cte on cte.FK_Table = q1.FK_Table and cte.depth = q1.Depth and cte.FK_Table != cte.PK_Table
--Builds the queries needs to unique identify records by a multi tennant identifier.
--This is only performed on tables that do not have the Multi tennant column in their definition
;
with QueryBuilder
as
( select FK_table --Table currently under evaluation
,PK_table --Table with Closest FK relationship
,PK_Column --Column on table linked to for FK relationship
,FK_Column --Column on FK dependent table for FK relationship
,ColumnPresent --If the multitennant id is available on th current table
,ColumnPresentPK -- if the multitennant id is available on the up stream joined FK table
,convert(varchar(max),FK_table) +','+ PK_table FullTblPath --entire path for fk join
,CONVERT(varchar(max),fk_column ) FullColPath --entire path for fk join
--Construction of join statement
,Convert(varchar(max),' from '+FK_table+' where '+FK_Column +' in ( Select '+FK_Column+' from '+FK_table +' join '
+ PK_table+ ' on '+FK_table +'.'+FK_Column+'='+PK_table+'.'+PK_Column ) JoinStmt
,FK_Column OriginalColumn
, 1 depth --Depth of recursion needed for current join
from @tblInfo q0 where q0.ColumnPresent =0
union all
select QueryBuilder.FK_table
,q1.PK_table
,q1.PK_Column
,q1.FK_Column
,q1.ColumnPresent
,q1.columnPresentPK
,FullTblPath+','+q1.PK_table FullTblPath
,FullColPath+','+q1.FK_Column FullColPath
,JoinStmt+Char(30)+' join '+ q1.PK_table+ ' on '+QueryBuilder.PK_table+'.'+q1.FK_Column+'='+q1.PK_table+'.'+q1.PK_Column JoinStmt
,QueryBuilder.FK_Column OriginalColumn
,QueryBuilder.depth +1 depth
from QueryBuilder
join @tblInfo q1 on QueryBuilder.PK_table = q1.FK_table
where q1.ColumnPresent !=1
)
--Selects all the queries produced appending the where clause for the filtering of multitennant clinics)
insert @FKRelationshipJoins
Select Querybuilder.FK_table
,PK_table
,PK_Column
,FK_Column
,ColumnPresent
,ColumnPresentPK
,FullTblPath
,FullColPath
,OriginalColumn
,Querybuilder.depth
,JoinStmt+' where '+@columnname+' = @tennantIdentifier'+')' JoinStmtfiltered
from (
select max(depth)Depth,FK_Table from querybuilder group by FK_Table) q1
join querybuilder on querybuilder.FK_Table = q1.FK_Table and querybuilder.depth = q1.Depth
where columnPresentPK = 1
select * from @TblInfo
order by depth desc
select * from @FKRelationshipJoins
--select FK_table,FullPath, (select FK_Column from @TblInfo q1 where q1.FK_Table=q2.FK_table and '%'+PK_table+'%' from @FKRelationshipJoins q2
--select distinct FK_Table from @Contstraints
--Retuns all Tables that have FK relationships but do not have the multitennant column identifier anywhere in their leaf nodes
select * from @TblInfo where FK_Table not in (select FK_Table from @FKRelationshipJoins)
and (select COUNT(*) from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = FK_Table and COLUMN_NAME = @columnname) =0
--Returns all base tables that have the specified Multi tennant Column in their columnlist but do not have a constraint creted for it
select * from INFORMATION_SCHEMA.TABLES t
where TABLE_NAME not in ( select fk_table from @TblInfo)
and (select COUNT(*) from INFORMATION_SCHEMA.COLUMNS c where c.TABLE_NAME = t.TABLE_NAME and c.COLUMN_NAME = @columnname) >=1
and TABLE_TYPE = 'Base Table'
--Returns all Tables that have constraints but are not being picked up by our Constrain mappings
--select * from @Contstraints q0 where FK_table not in(select fk_table from @TblInfo q1 )
--Master Join Statement. This statement is current tailored for a delete but it could be easily
-- changed to be and other type of DML by simply changing the statement prefixing the Join stmnt and the sort order
select FK_Table,'delete '+JoinStmt+'--LeafNode depth:'+Convert(varchar(2),depth) JoinStmt ,depth from (
select q1.FK_Table
,coalesce(q2.JoinStmt,(' from '+ q1.FK_table+' where '+ @columnname+'=@tennantIdentifier')) as JoinStmt
,Coalesce(q1.Depth,'0' ) depth from @TblInfo q1
left join @FKRelationshipJoins q2 on q2.FK_table=q1.FK_Table
union all
select TABLE_NAME,' from '+ TABLE_NAME+' where '+ @columnname+'=@tennantIdentifier ','0' depth from INFORMATION_SCHEMA.TABLES t
where TABLE_NAME not in ( select fk_table from @TblInfo)
and (select COUNT(*) from INFORMATION_SCHEMA.COLUMNS c where c.TABLE_NAME = t.TABLE_NAME and c.COLUMN_NAME = @columnname) >=1
and TABLE_TYPE = 'Base Table') q4
where FK_Table not in
( select FK_Table from @TblInfo where FK_Table not in (select FK_Table from @FKRelationshipJoins)
and (select COUNT(*) from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = FK_Table and COLUMN_NAME = @columnname) =0)
order by q4.depth
--Desc For delete ASC for insert - Updates are tricy and cannot be guaranteed to work.
desc
--asc