Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2008
    Posts
    24

    Unanswered: Dropping the parent table

    Hi,

    I need to drop some tables in the database.In which i need to drop the parent table first and then the child table.

    I used the following statements to disable the constraints and drop the table but still it is throwing the error there is a foreign key dependency.

    exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
    exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

    Please let me know if there is anyway out to solve this.

    Kiran

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why can't you just drop the child table first?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Dec 2008
    Posts
    24
    Thanks for replying.

    I actually have a dynamic tables .so i can't get the tables in a sequence so that i can't drop the child table first.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "DROP TABLE cannot be used to drop a table that is referenced by a FOREIGN KEY constraint. The referencing FOREIGN KEY constraint or the referencing table must first be dropped. If both the referencing table and the table that holds the primary key are being dropped in the same DROP TABLE statement, the referencing table must be listed first."
    So sayeth books online.
    You could write your script to peruse the system tables and drop the constraints first...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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