Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003

    Unanswered: ignoring keys while Truncating tables

    Hello everyone.

    I'm working with a customers business application which is developed in MSSQL. The system has over 170 tables and there is no documentation by those who created it. Now the problem is that I have to write script that deletes all the data in all the tables but since there are foreign keys defined in the tables I can't delete the data. Ofcourse I can figure it out eventually by testing back and forth in which order I have to delete the data in the tables but since there are over 170 tables that could take a very long time.

    Does anyone now how I can solve this?? is there for examaple a way to make SQL server to ignore the foreign key lookup? What can I do?? is there any way which I can see in what order I should delete the data in the tables???

    appritiate any help or comments.



  2. #2
    Join Date
    Feb 2002
    Houston, TX
    1. script out your deletes and run the script 170 times, eventualy you will clear out all the tables.

    2. use Enterprise Manager to generate a diagram of all the tables in the database. This would tell you the exact order you would need to follow to get everything deleted.

    3. in Enterprise Manager and Query Analyzer you can look up the dpendencies of any object. In EM right click on any object, select all tasks, dependencies, in QA press "F8" to show the object browser, select and object drill down till you see dependencies.

    4. use sp_depends on all 170 tables/view to figure out the parents and or child relationships.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jul 2002
    If you are going to have to reload the database you are going to want to know your foreign keys.

  4. #4
    Join Date
    Aug 2002
    For reference of cascade delete refer to SQL TEam link.

    Refer to this Code and modify to accomplish the task.

    --Satya SKJ
    Microsoft SQL Server MVP

  5. #5
    Join Date
    Apr 2003
    disable your fk checking before deleting data, and re-enable them after.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts