Query to find the tables order in which data needs to be deleted because of PrimaryKey and ForeignKey setup in a database RRS feed

  • Question

  • ;With a as 
    	Select 0 as lvl, t.object_id as tblID 
        from sys.Tables t
        Where t.is_ms_shipped=0
          and t.object_id not in (Select f.referenced_object_id from sys.foreign_keys f)
       UNION ALL
    	Select a.lvl + 1 as lvl, f.referenced_object_id as tblId
        from a
    		inner join sys.foreign_keys f 
    			on a.tblId = f.parent_object_id 
    				and a.tblID <> f.referenced_object_id
    SELECT  DB_Name()  AS DBName,object_schema_name(tblID) AS  SchemaName,object_name(tblId) AS TableName
    	,'['+ DB_Name()+ '].['+ object_schema_name(tblID) + '].[' + object_name(tblId) + ']'  AS FullObjectName
    	, 'Delete from ['+ DB_Name()+ '].['+ object_schema_name(tblID) + '].[' + object_name(tblId) + ']' AS DeleteCommand
    FROM a
    GROUP  BY  tblId 
    Order by Max(lvl),1

    I have the above query which is giving the order in which data needs to be deleted to avoid Pkey and FKey violation errors and i am assuming its returning correct results.

    I am looking for additional columns in output like ParentTable, Child Table etc along with the order.

    Any help is appreciated. Thanks

    Friday, May 22, 2020 12:58 PM

All replies

  • Please provide DDL+DML so we will be able to reproduce the scenario 

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Friday, May 22, 2020 4:25 PM
  • Can you please explain what you're trying to do? We have no DDL or even a description in narrative form of what you're trying to do. I'm going to guess that in some vague generic way you're trying to remove weak entities from the schema when their strong entity is deleted. This would be things like removing all the order details when an order is removed . If so, you should have done this with a

    REFERENCES <strong entity>

    DRI constraint and never had this problem in the first place.

    Finally, the terms "parent" and "child" are from network databases, not RDBMS; the correct terms are referenced and referencing tables.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, May 23, 2020 7:48 PM
  • Hi Leo00,


    Could you  please share us your table structure and some sample data along with your expected result? So that we’ll get a right direction and make some test.

    By the way , Please share us your error message .Thanks in advance .

    Best Regards,


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 25, 2020 6:04 AM