How can I drop a table if there is a foreign key constraint in SQL Server

Question

I have the following   DROP TABLE  dbo   ExtraUserInformation   DROP TABLE  dbo   UserProfile   DROP TABLE  dbo   webpages Membership   DROP TABLE  dbo   webpages OAuthMembership   DROP TABLE  dbo   webpages Roles   DROP TABLE  dbo   webpages UsersInRoles    CREATE TABLE  dbo   ExtraUserInformation         Id        INT            IDENTITY  1  1  NOT NULL       UserId    INT            NOT NULL       FullName  NVARCHAR  MAX  NULL       Link      NVARCHAR  MAX  NULL       Verified  BIT            NULL      CONSTRAINT  PK dbo ExtraUserInformation  PRIMARY KEY CLUSTERED   Id  ASC      CREATE TABLE  dbo   webpages UsersInRoles         UserId  INT NOT NULL       RoleId  INT NOT NULL      PRIMARY KEY CLUSTERED   UserId  ASC   RoleId  ASC       CONSTRAINT  fk UserId  FOREIGN KEY   UserId   REFERENCES  dbo   UserProfile    UserId        CONSTRAINT  fk RoleId  FOREIGN KEY   RoleId   REFERENCES  dbo   webpages Roles    RoleId        However this is failing with a message saying   Msg 3726  Level 16  State 1  Line 6 Could not drop object  dbo UserProfile  because it is referenced by a FOREIGN KEY constraint  Msg 3726  Level 16  State 1  Line 9 Could not drop object  dbo webpages Roles  because it is referenced by a FOREIGN KEY constraint  Msg 2714  Level 16  State 6  Line 27 There is already an object named  UserProfile  in the database  Checking identity information  current identity value  NULL   current column value  NULL   DBCC execution completed  If DBCC printed error messages  contact your system administrator    How can I drop a table in these circumstances

User · Answer

--Find and drop the constraints      DECLARE  dynamicSQL VARCHAR MAX      DECLARE MY CURSOR CURSOR       LOCAL STATIC READ ONLY FORWARD ONLY      FOR         SELECT dynamicSQL    ALTER TABLE       OBJECT SCHEMA NAME parent object id            OBJECT NAME parent object id       DROP CONSTRAINT      name               FROM sys foreign keys         WHERE object name referenced object id   in   table1    table2    table3       OPEN MY CURSOR     FETCH NEXT FROM MY CURSOR INTO  dynamicSQL     WHILE   FETCH STATUS   0     BEGIN          PRINT  dynamicSQL         EXEC   dynamicSQL           FETCH NEXT FROM MY CURSOR INTO  dynamicSQL     END     CLOSE MY CURSOR     DEALLOCATE MY CURSOR       -- Drop tables     DROP  table1      DROP  table2      DROP  table3

User · Answer

BhupeshC and murat   this is what I was looking for  However  SQL varchar 4000  wasn t big enough  So  small change  DECLARE  cmd varchar 4000   DECLARE MY CURSOR CURSOR    LOCAL STATIC READ ONLY FORWARD ONLY FOR   select  ALTER TABLE    s name       t name    DROP CONSTRAINT      RTRIM f name        FROM sys Tables t INNER JOIN sys foreign keys f ON f parent object id   t object id INNER JOIN sys schemas s ON s schema id   f schema id  OPEN MY CURSOR FETCH NEXT FROM MY CURSOR INTO  cmd WHILE   FETCH STATUS   0 BEGIN     -- EXEC   cmd      PRINT  cmd     FETCH NEXT FROM MY CURSOR INTO  cmd END CLOSE MY CURSOR DEALLOCATE MY CURSOR  GO

User · Answer

You must drop the constraint before you can drop the table Other wise its rule violation  How to get  foreign key relationships see this old question  SQL DROP TABLE foreign key constraint

User · Answer

1-firstly  drop the foreign key constraint after that drop the tables   2-you can drop all foreign key via executing the following query   DECLARE  SQL varchar 4000     SELECT  SQL     SQL    ALTER TABLE     s name     t name     DROP CONSTRAINT      RTRIM f name          CHAR 13  FROM sys Tables t INNER JOIN sys foreign keys f ON f parent object id   t object id INNER JOIN sys schemas     s ON s schema id   f schema id  --EXEC   SQL   PRINT  SQL   if you execute the printed results  SQL  the foreign keys will be dropped

User · Answer

The Best Answer to dropping the table containing foreign constraints is     Step 1   Drop the Primary key of the table   Step 2   Now it will prompt whether to delete all the foreign references or not   Step 3   Delete the table

User · Answer

To drop a table if there is a foreign key constraint in MySQL Server   Run the sql query    SET FOREIGN KEY CHECKS   0  DROP TABLE table name  Hope it helps

User · Answer

Type this      SET foreign key checks   0  delete your table then type SET foreign key checks   1   MySQL     Temporarily disable Foreign Key Checks or Constraints

User · Answer

You have to drop the constraint before drop your table   You can use those queries to find all FKs in your table and find the FKs in the tables in which your table is used   Declare  SchemaName VarChar 200     Your Schema name  Declare  TableName VarChar 200     Your Table Name   -- Find FK in This table  SELECT        IF  EXISTS  SELECT   FROM sys foreign keys WHERE object id    OBJECT ID N                   OBJECT SCHEMA NAME FK parent object id            FK name              AND parent object id   OBJECT ID N                   OBJECT SCHEMA NAME FK parent object id             OBJECT NAME FK parent object id                          ALTER TABLE      OBJECT SCHEMA NAME FK parent object id               OBJECT NAME FK parent object id            DROP CONSTRAINT     FK name       S name   O name  OBJECT NAME FK parent object id  FROM sys foreign keys AS FK INNER JOIN Sys objects As O    ON  O object id   FK parent object id   INNER JOIN SYS schemas AS S    ON  O schema id   S schema id    WHERE        O name    TableName       And S name    SchemaName   -- Find the FKs in the tables in which this table is used   SELECT        IF  EXISTS  SELECT   FROM sys foreign keys WHERE object id            OBJECT ID N                   OBJECT SCHEMA NAME FK parent object id            FK name                AND parent object id   OBJECT ID N                   OBJECT SCHEMA NAME FK parent object id              OBJECT NAME FK parent object id                           ALTER TABLE      OBJECT SCHEMA NAME FK parent object id               OBJECT NAME FK parent object id            DROP CONSTRAINT     FK name       S name   O name  OBJECT NAME FK parent object id  FROM sys foreign keys AS FK INNER JOIN Sys objects As O    ON  O object id   FK referenced object id   INNER JOIN SYS schemas AS S    ON  O schema id   S schema id    WHERE        O name    TableName       And S name    SchemaName

[sql-server] How can I drop a table if there is a foreign key constraint in SQL Server?

Examples related to sql-server