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

Kenny Gua 411 Reputation points
2023-03-30T14:26:55.4+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,872 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 102.2K Reputation points
    2023-04-12T21:18:48.9433333+00:00

    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.


5 additional answers

Sort by: Most helpful
  1. Timothy Horrocks 75 Reputation points
    2023-03-30T20:26:59.3666667+00:00
    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
    
    0 comments No comments

  2. Erland Sommarskog 102.2K Reputation points
    2023-03-30T21:38:41.91+00:00

    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.


  3. CosmogHong-MSFT 23,561 Reputation points Microsoft Vendor
    2023-03-31T03:21:54.44+00:00

    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.

    0 comments No comments

  4. Kenny Gua 411 Reputation points
    2023-04-12T13:20:42.6733333+00:00

    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%'

    0 comments No comments