SQL Feather and Quill

Recently, I was tasked with writing a job that would delete data — with a bunch of rules on what could be deleted and what couldn’t.  I thought it was going to be fairly straight forward, but the finer points of the situation made it quite complicated.

The Situation:

The diagram below works as an example of the situation.  Sometimes I needed to delete data from Table1 as well as all other dependent data, sometimes I needed to delete it from Table3, sometimes from Table4, etc.  I wrote the procedures to do delete each set of dependent data, but I was never sure if I had gotten it all – the real life situation wasn’t this linear and sometimes tests would fail and I would add another table to my deletion steps.  I needed a script…

The Setup:

I decided to create the situation above and see if I could come up with a script that would identify PKs that would cause my deletions to fail.

Here’s the script:

CREATE TABLE [dbo].[Table1](
[T1ID] [int] NOT NULL,
CONSTRAINT [T1ID_PK] PRIMARY KEY CLUSTERED
(
[T1ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
Go
CREATE TABLE [dbo].[Table9](
[T9ID] [int] NOT NULL,
CONSTRAINT [T9ID_PK] PRIMARY KEY CLUSTERED
(
[T9ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
Go
CREATE TABLE [dbo].[Table3](
[T3ID] [int] NOT NULL,
[T1ID] [int] NULL,
[T9ID] [int] NULL,
CONSTRAINT [T3ID_PK] PRIMARY KEY CLUSTERED
(
[T3ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
Go
CREATE TABLE [dbo].[Table2](
[T2ID] [int] NOT NULL,
[T1ID] [int] NULL,
CONSTRAINT [T2ID_PK] PRIMARY KEY CLUSTERED
(
[T2ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
Go
CREATE TABLE [dbo].[Table7](
[T7ID] [int] NOT NULL,
[T3ID] [int] NULL,
CONSTRAINT [T7ID_PK] PRIMARY KEY CLUSTERED
(
[T7ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
Go
CREATE TABLE [dbo].[Table6](
[T6ID] [int] NOT NULL,
[T3ID] [int] NULL,
CONSTRAINT [T6ID_PK] PRIMARY KEY CLUSTERED
(
[T6ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
Go
CREATE TABLE [dbo].[Table5](
[T5ID] [int] NOT NULL,
[T2ID] [int] NULL,
CONSTRAINT [T5ID_PK] PRIMARY KEY CLUSTERED
(
[T5ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
Go
CREATE TABLE [dbo].[Table4](
[T4ID] [int] NOT NULL,
[T2ID] [int] NULL,
CONSTRAINT [T4ID_PK] PRIMARY KEY CLUSTERED
(
[T4ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
Go
CREATE TABLE [dbo].[Table8](
[T8ID] [int] NOT NULL,
[T4ID] [int] NULL,
CONSTRAINT [T8ID_PK] PRIMARY KEY CLUSTERED
(
[T8ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
Go
ALTER TABLE [dbo].[Table2]  WITH CHECK ADD  CONSTRAINT [FK_Table2_Table1] FOREIGN KEY([T1ID])
REFERENCES [dbo].[Table1] ([T1ID])
Go
ALTER TABLE [dbo].[Table2] CHECK CONSTRAINT [FK_Table2_Table1]
Go
ALTER TABLE [dbo].[Table3]  WITH CHECK ADD  CONSTRAINT [FK_Table3_Table1] FOREIGN KEY([T1ID])
REFERENCES [dbo].[Table1] ([T1ID])
Go
ALTER TABLE [dbo].[Table3] CHECK CONSTRAINT [FK_Table3_Table1]
Go
ALTER TABLE [dbo].[Table3]  WITH CHECK ADD  CONSTRAINT [FK_Table3_Table9] FOREIGN KEY([T9ID])
REFERENCES [dbo].[Table9] ([T9ID])
Go
ALTER TABLE [dbo].[Table3] CHECK CONSTRAINT [FK_Table3_Table9]
Go
ALTER TABLE [dbo].[Table4]  WITH CHECK ADD  CONSTRAINT [FK_Table4_Table2] FOREIGN KEY([T2ID])
REFERENCES [dbo].[Table2] ([T2ID])
Go
ALTER TABLE [dbo].[Table4] CHECK CONSTRAINT [FK_Table4_Table2]
Go
ALTER TABLE [dbo].[Table5]  WITH CHECK ADD  CONSTRAINT [FK_Table5_Table2] FOREIGN KEY([T2ID])
REFERENCES [dbo].[Table2] ([T2ID])
Go
ALTER TABLE [dbo].[Table5] CHECK CONSTRAINT [FK_Table5_Table2]
Go
ALTER TABLE [dbo].[Table6]  WITH CHECK ADD  CONSTRAINT [FK_Table6_Table3] FOREIGN KEY([T3ID])
REFERENCES [dbo].[Table3] ([T3ID])
Go
ALTER TABLE [dbo].[Table6] CHECK CONSTRAINT [FK_Table6_Table3]
Go
ALTER TABLE [dbo].[Table7]  WITH CHECK ADD  CONSTRAINT [FK_Table7_Table3] FOREIGN KEY([T3ID])
REFERENCES [dbo].[Table3] ([T3ID])
Go
ALTER TABLE [dbo].[Table7] CHECK CONSTRAINT [FK_Table7_Table3]
Go
ALTER TABLE [dbo].[Table8]  WITH CHECK ADD  CONSTRAINT [FK_Table8_Table4] FOREIGN KEY([T4ID])
REFERENCES [dbo].[Table4] ([T4ID])
Go
ALTER TABLE [dbo].[Table8] CHECK CONSTRAINT [FK_Table8_Table4]
Go

The Script:

I looked around the old information super highway and found Pinal Dave (Blog/Twitter) had something that fit into what I was looking for here:

SQL SERVER – Query to Display Foreign Key Relationships and Name of the Constraint for Each Table in Database

I tweaked it and used the ABCs of CTEs (from Jason Strate (Blog/Twitter) and came up with the following script to identify tables which have primary keys to the initial table, so you will never forget a table in a deletion again :

DECLARE @TableName VARCHAR(255) = 'Table1' --Change this to find the different situations.

;WITH FKStructure  (K_Table, FK_Column, PK_Table, PK_Column, Constraint_Name, Level)
as
(-- Anchor member definition
SELECT
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME,
1 AS Level
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
WHERE PK.TABLE_NAME= @TableName
UNION ALL
-- Recursive member definition
SELECT
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME,
Level + 1
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
JOIN FKStructure FKS on FKS.K_Table = PK.TABLE_NAME)

Select * from FKStructure