Help in query to show Foreign/primary key for specific table with Seq_Number

Question

Hi, Example: I have to delete a record from table-Employee but not able to delete because that record has foreign key from other table-Employee_Dep. So I will have to delete the record from Employee_Dep then I can bel able to delete record table-Employee. But most of the cases the foriign key in many tables. So I will have to delete one by one then can be able to delete completely and successfully from all table. Is there query to see any primary/foreign key of table-Employee (for example) and should shows all the tables which has foreign keys/mapping for table-Employee with "Sequence_ Number". So it will be east to see all tables and can delete one by one with sequence_Number and without any error.

Answer 1

Here is a query that produces DELETE statement. I'm a little uncertain whether MIN maybe should be MAX. With some luck it may not matter.

WITH rekurs AS (
   SELECT object_id('YourSchema.YourTable') AS table_id, level = 1
   UNION ALL
   SELECT fk.parent_object_id, level = level + 1
   FROM   sys.foreign_keys fk
   JOIN   rekurs r ON fk.referenced_object_id = r.table_id
)
SELECT 'DELETE ' + quotename(s.name) + '.' + quotename(t.name) AS delstmt, MIN(r.level) AS level
FROM   rekurs r
JOIN   sys.tables t ON r.table_id = t.object_id
JOIN   sys.schemas s ON s.schema_id = t.schema_id
GROUP  BY s.name, t.name
ORDER  BY level DESC, s.name, t.name

You may get this error message:

Msg 530, Level 16, State 1, Line 27 The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

That would happen if you have circular dependencies. That is table A has an FK to B and B has an FK to A.

Answer 2

you can add a where clause to filter on the referenced table
select 
	sfk.name as ForeignKey, 
	ss.name + '.' + st.name as ParentTable, 
	sc.name as ParentColumn,
	ss2.name + '.' + st2.name as ReferencedTable,
	sc2.name as ReferencedColumn
from 
	sys.Foreign_Keys sfk (nolock)
inner join 
	sys.tables st  (nolock) on sfk.parent_object_id = st.object_id
inner join 
	sys.tables st2 (nolock) on sfk.referenced_object_id = st2.object_id
inner join
	sys.foreign_key_columns sfkc (nolock) on sfkc.constraint_object_id = sfk.object_id
inner join 
	sys.columns sc (nolock) on st.object_id = sc.object_id and sc.column_id = sfkc.parent_column_id
inner join 
	sys.columns sc2 (nolock) on st2.object_id = sc2.object_id and sfkc.referenced_column_id = sc2.column_id
join 
	sys.schemas ss (nolock) on st.schema_id = ss.schema_id
join 
	sys.schemas ss2 (nolock) on st2.schema_id = ss2.schema_id
order by
	st2.[name] asc

Answer 3

Not a direct answer to your question, but a way out of this quagmire is to set up the foreign keys with ON DELETE. The you only need to delete the master row, and all other rows will cascade. Here is a quick demo:

USE tempdb
go
CREATE TABLE Orders (OrderID int NOT NULL,
                     OrderData varchar(50) NOT NULL,
                     CONSTRAINT pk_Orders PRIMARY KEY (OrderID)
)
CREATE TABLE OrderRows (OrderID int NOT NULL,
                        RowNo   int NOT NULL,
                        RowData varchar(50) NOT NULL,
                        CONSTRAINT pk_OrderRows PRIMARY KEY(OrderID, RowNo),
                        CONSTRAINT fk_OrderRows_Orders 
                           FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
                           ON DELETE CASCADE
)

go 
INSERT Orders(OrderID, OrderData)
   VALUES(1, 'Order one'), (2, 'Order two'), (3, 'Third order')
INSERT OrderRows (OrderID, RowNo, RowData)
   VALUES(1, 1, 'First row on first order'), (1, 2, 'Second row on first order'),
         (2, 1, 'First row on second order'), (2, 2, 'Second row on second order'),
         (3, 1, 'First row on third order'), (3, 2, 'Second row'), (3, 3, 'Third row')
go
DELETE Orders WHERE OrderID = 2
go
SELECT * FROM Orders
SELECT * FROM OrderRows
go
DROP TABLE OrderRows, Orders

I should add some caveats. ON CASCADE DELETE is not suitable for all situations. For instance, the CustomerID column in the Orders table should probably not have cascading delete. If you attempt to delete a customer that has orders, the business side may not like that both customers and orders are deleted. There are also situations where SQL Server disallows cascading foreign keys, because it finds it too difficlt.

Answer 4

Hi @Kenny Gua

Here is a stored procedure to "output the FK tree" via recursion, here is the code.txt.

Also, you could generate the delete script dynamically.

Please refer to this article for more details: Script to Delete Data from SQL Server Tables with Foreign Key Constraints.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

I added the following where clause in the above query of Timothy Horrocks in the bottom and getting all the foreign key related tables but how will know which should delete 1st and which should delete next because of dependency. Is there any way to identify the sequence if wants to delete completely records with sequence without any error

I just wants to delete the child records first and then delete the parent record in last so all delete will be successful without any foreign key issue. That's why wants to figure it out which wants to delete first. where ss.name +'.' + st.name like '%Employee%'