FK Dependency Tree map/ Script Generator

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