SQL DROP TABLE foreign key constraint

Question

If I want to delete all the tables in my database like this  will it take care of the foreign key constraint  If not  how do I take care of that first   GO IF OBJECT ID  dbo  Course    U   IS NOT NULL     DROP TABLE dbo  Course  GO IF OBJECT ID  dbo  Student    U   IS NOT NULL     DROP TABLE dbo  Student

User · Answer

execute the below code to get the foreign key constraint name which blocks your drop. For example, I take the roles table.

      SELECT *
      FROM sys.foreign_keys
      WHERE referenced_object_id = object_id('roles');

      SELECT name AS 'Foreign Key Constraint Name',
      OBJECT_SCHEMA_NAME(parent_object_id) + '.' + OBJECT_NAME(parent_object_id)
      AS 'Child Table' FROM sys.foreign_keys
      WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo'
      AND OBJECT_NAME(referenced_object_id) = 'dbo.roles'

you will get the FK name something as below : FK__Table1__roleId__1X1H55C1

now run the below code to remove the FK reference got from above.

ALTER TABLE dbo.users drop CONSTRAINT FK__Table1__roleId__1X1H55C1;

Done!

User · Answer

If you want to DROP a table which has been referenced by other table using the foreign key use  DROP TABLE  table name  CASCADE CONSTRAINTS  I think it should work for you

User · Answer

If it is SQL Server you must drop the constraint before you can drop the table

User · Answer

If you drop the  child  table first  the foreign key will be dropped as well  If you attempt to drop the  parent  table first  you will get an  Could not drop object  a  because it is referenced by a FOREIGN KEY constraint   error

User · Answer

Here s another way to do delete all the constraints followed by the tables themselves  using a concatenation trick involving FOR XML PATH     which allows merging multiple input rows into a single output row    Should work on anything SQL 2005  amp  later   I ve left the EXECUTE commands commented out for safety   DECLARE  SQL NVARCHAR max   WITH fkeys AS       SELECT quotename s name          quotename o name  tablename  quotename fk name  constraintname      FROM sys foreign keys fk     JOIN sys objects o ON fk parent object id   o object id     JOIN sys schemas s ON o schema id   s schema id   SELECT  SQL   STUFF  SELECT    ALTER TABLE     tablename     DROP CONSTRAINT     constraintname FROM fkeys FOR XML PATH      1 2      -- EXECUTE  sql   SELECT  SQL   STUFF  SELECT    DROP TABLE     quotename TABLE SCHEMA          quotename TABLE NAME   FROM INFORMATION SCHEMA TABLES  FOR XML PATH      1 2      -- EXECUTE  sql

User · Answer

Slightly more generic version of what  mark s posted  this helped me  SELECT   ALTER TABLE      OBJECT SCHEMA NAME k parent object id           OBJECT NAME k parent object id        DROP CONSTRAINT     k name FROM sys foreign keys k WHERE referenced object id   object id  your table     just plug your table name  and execute the result of it

User · Answer

No  this will not drop your table if there are indeed foreign keys referencing it   To get all foreign key relationships referencing your table  you could use this SQL  if you re on SQL Server 2005 and up    SELECT    FROM sys foreign keys WHERE referenced object id   object id  Student     and if there are any  with this statement here  you could create SQL statements to actually drop those FK relations   SELECT       ALTER TABLE       OBJECT SCHEMA NAME parent object id                OBJECT NAME parent object id            DROP CONSTRAINT      name       FROM sys foreign keys WHERE referenced object id   object id  Student

User · Answer

Removing Referenced FOREIGN KEY Constraints Assuming there is a parent and child table Relationship in SQL Server   --First find the name of the Foreign Key Constraint    SELECT      FROM sys foreign keys   WHERE referenced object id   object id  States    --Then Find foreign keys referencing to dbo Parent States  table     SELECT name AS  Foreign Key Constraint Name               OBJECT SCHEMA NAME parent object id          OBJECT NAME parent object id  AS  Child Table     FROM sys foreign keys     WHERE OBJECT SCHEMA NAME referenced object id     dbo  AND                OBJECT NAME referenced object id     dbo State    -- Drop the foreign key constraint by its name     ALTER TABLE dbo cities DROP CONSTRAINT FK  cities  state  6442E2C9    -- You can also use the following T-SQL script to automatically find   --and drop all foreign key constraints referencing to the specified parent   -- table    BEGIN  DECLARE  stmt VARCHAR 300    -- Cursor to generate ALTER TABLE DROP CONSTRAINT statements    DECLARE cur CURSOR FOR  SELECT  ALTER TABLE     OBJECT SCHEMA NAME parent object id            OBJECT NAME parent object id                      DROP CONSTRAINT     name  FROM sys foreign keys   WHERE OBJECT SCHEMA NAME referenced object id     dbo  AND              OBJECT NAME referenced object id     states     OPEN cur   FETCH cur INTO  stmt    -- Drop each found foreign key constraint    WHILE   FETCH STATUS   0   BEGIN     EXEC   stmt       FETCH cur INTO  stmt    END    CLOSE cur    DEALLOCATE cur     END   GO  --Now you can drop the parent table    DROP TABLE states  --  Command s  completed successfully

User · Answer

If I want to delete all the tables in   my database   Then it s a lot easier to drop the entire database   DROP DATABASE WorkerPensions

User · Answer

Here is another way to drop all tables correctly  using sp MSdropconstraints procedure  The shortest code I could think of   exec sp MSforeachtable  declare  name nvarchar max   set  name   parsename      1   exec sp MSdropconstraints  name   exec sp MSforeachtable  drop table

User · Answer

In SQL Server Management Studio 2008  R2   and newer  you can Right Click on the      DB -  Tasks -  Generate Scripts    Select the tables you want to DROP  Select  Save to new query window   Click on the Advanced button  Set Script DROP and CREATE to Script DROP  Set Script Foreign Keys to True  Click OK  Click Next -  Next -  Finish  View the script and then Execute

User · Answer

Here is a complete script to implement a solution   create Procedure  dev  DeleteTablesFromSchema        schemaName varchar 500    As  begin     declare  constraintSchemaName nvarchar 128    constraintTableName nvarchar 128     constraintName nvarchar 128      declare  sql nvarchar max      -- delete FK first     declare cur1 cursor for     select distinct      CASE WHEN t2  object id  is NOT NULL  THEN  s2 name ELSE s name END as SchemaName      CASE WHEN t2  object id  is NOT NULL  THEN  t2 name ELSE t name END as TableName      CASE WHEN t2  object id  is NOT NULL  THEN  OBJECT NAME d2 constraint object id  ELSE OBJECT NAME d constraint object id  END as ConstraintName     from sys objects t          inner join sys schemas s              on t  schema id    s  schema id          left join sys foreign key columns d              on  d parent object id   t  object id          left join sys foreign key columns d2              on  d2 referenced object id   t  object id          inner join sys objects t2              on  d2 parent object id   t2  object id          inner join sys schemas s2              on  t2  schema id    s2  schema id      WHERE t  type   U           AND t2  type   U          AND t is ms shipped   0          AND t2 is ms shipped   0          AND s Name  schemaName     open cur1     fetch next from cur1 into  constraintSchemaName   constraintTableName   constraintName     while   fetch status   0     BEGIN         set  sql   ALTER TABLE      constraintSchemaName          constraintTableName   DROP CONSTRAINT    constraintName             exec  sql          fetch next from cur1 into  constraintSchemaName   constraintTableName   constraintName     END     close cur1     deallocate cur1      DECLARE  tableName nvarchar 128      declare cur2 cursor for     select s Name  p Name     from sys objects p         INNER JOIN sys schemas s ON p  schema id    s  schema id      WHERE p  type   U  and is ms shipped   0      AND s Name  schemaName     ORDER BY s Name  p Name     open cur2      fetch next from cur2 into  schemaName  tableName     while   fetch status   0     begin         set  sql   DROP TABLE      schemaName          tableName         exec  sql          fetch next from cur2 into  schemaName  tableName     end      close cur2     deallocate cur2  end go

User · Answer

If you are on a mysql server and if you don t mind loosing your tables  you can use a simple query to delete multiple tables at once   SET foreign key checks   0  DROP TABLE IF EXISTS table a table b table c table etc  SET foreign key checks   1    In this way it doesn t matter in what order you use the table in you query   If anybody is going to say something about the fact that this is not a good solution if you have a database with many tables  I agree

User · Answer

Using SQL Server Manager you can drop foreign key constraints from the UI  If you want to delete the table Diary but the User table has a foreign key DiaryId pointing to the Diary table  you can expand  using the plus symbol  the User table and then expand the Foreign Keys section  Right click on the foreign key that points to the diary table then select Delete  You can then expand the Columns section  right click and delete the column DiaryId too  Then you can just run   drop table Diary     I know your actual question is about deleting all tables  so this may not be a useful for that case  However  if you just want to delete a few tables this is useful I believe  the title does not explicitly mention deleting all tables

[sql-server] SQL DROP TABLE foreign key constraint

Examples related to sql-server

Examples related to foreign-keys

Examples related to constraints

Examples related to database-table

Examples related to drop-table