Sql query to forcefully drop a table even if foreign key constraint on table in sql server

How to write a sql query which will forcefully drop or delete a given or specified table even if there is foreign key constraint on table in sql server

--Change the table name

DECLARE @TableName AS VARCHAR(100) = 'tblStudent'

DECLARE @DropQuery AS VARCHAR(2000) = ''

SELECT @DropQuery = @DropQuery +

'ALTER TABLE [' + @TableName + ']

 DROP CONSTRAINT [' + name + ']; '

 FROM sys.foreign_keys

 WHERE  parent_object_id = OBJECT_ID(@TableName)

SELECT @DropQuery = @DropQuery +

'ALTER TABLE [' + OBJECT_NAME(parent_object_id) +']

 DROP CONSTRAINT [' + name + ']; '

 FROM sys.foreign_keys

 WHERE  referenced_object_id =OBJECT_ID(@TableName)

SET @DropQuery = @DropQuery + 'DROP TABLE [' + @TableName + ']'

EXECUTE(@DropQuery)

Better solution

Solution of this problem we will have to drop all related tables in correct order or we will have to delete the all foreign key constraints on this table. Otherwise we will get error message:

Could not drop object '' because it is referenced by a FOREIGN KEY constraint.

So I am writing a stored procedure. This stored procedure can forcefully delete the tables or generate script event if there is foreign key constraints with different table. Parameters:

@vcTableName: It is the name of table to which you want to drop or generate table drop scrip.

@ntOption: It can be 0, 1, 2 or 3. 0 is default value.

Options	Purpose
0	To generate the drop table script by dropping the all foreign key referenced tables.
1	To drop the table by dropping the all foreign key referenced tables.
2	To generate the script to drop the table by dropping all foreign key constraint on it.
3	To drop the table by dropping all foreign key constraint on it.

CREATE PROC uspForeceDrop(

     @vcTableName AS VARCHAR(500),

     @ntOption AS INT = 0

)

AS

BEGIN

     DECLARE @vcDropQuey AS VARCHAR(MAX)

    

     BEGIN TRY

    

          --To validate the fisrt parameter of procedure

          IF OBJECT_ID(@vcTableName) IS NULL

              RAISERROR('Givent table name doesn''t exists in current database.',16,1)

             

          --To validate the second parameter of procedure

          IF @ntOption NOT IN (0,1,2,4)

              RAISERROR('Invalid options has passed. .',16,1)

             

          IF @ntOption IN (0,1) BEGIN

         

              --Using recursive cte getting the all referenced tables

              ;WITH cteFk as(

              SELECT

                   QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' +

                   QUOTENAME(OBJECT_NAME(parent_object_id)) AS vcTableName,

                  

                   0 AS ntLevel 

              FROM sys.foreign_keys fk 

              WHERE referenced_object_id = OBJECT_ID(@vcTableName)

              UNION ALL

              SELECT

                   QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' +

                    QUOTENAME(OBJECT_NAME(parent_object_id)),

                  

                   ntLevel + 1 

              FROM sys.foreign_keys fk INNER JOIN cteFk

              ON fk.referenced_object_id = OBJECT_ID(cteFk.vcTableName)

              ),

              cteQuery AS(

                   SELECT

                        vcTableName,

                        MAX(ntLevel) AS ntOrder 

                   FROM cteFk GROUP BY vcTableName

              )SELECT

                   @vcDropQuey = ISNULL(@vcDropQuey,'') + 'DROP TABLE ' + vcTableName + ';'

              FROM cteQuery

              ORDER BY ntOrder DESC

              --Generating the drop table query

              SET @vcDropQuey = ISNULL(@vcDropQuey,'')

                   + 'DROP TABLE ' +

                   QUOTENAME(OBJECT_SCHEMA_NAME(OBJECT_ID(@vcTableName))) + '.'

                   + QUOTENAME(OBJECT_NAME(OBJECT_ID(@vcTableName)))

             

              IF @ntOption = 0

                   SELECT @vcDropQuey AS vcDropQuery

              ELSE IF @ntOption = 1 --Droping the tables

                   EXECUTE (@vcDropQuey)

             

          END ELSE IF @ntOption IN (2,3) BEGIN

         

              --Getting the all foreign key constraints on a table

              SELECT

             

                   @vcDropQuey = ISNULL(@vcDropQuey,'') + 'ALTER TABLE ' +

                   QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.'

                   + QUOTENAME(OBJECT_NAME(parent_object_id)) +

                   ' DROP CONSTRAINT ' + QUOTENAME(name) + ';'

                  

              FROM sys.foreign_keys fk 

              WHERE referenced_object_id = OBJECT_ID(@vcTableName)

             

              --Generating the drop table query

              SET @vcDropQuey = ISNULL(@vcDropQuey,'') + 'DROP TABLE '

                   + QUOTENAME(OBJECT_SCHEMA_NAME(OBJECT_ID(@vcTableName))) + '.'

                   + QUOTENAME(OBJECT_NAME(OBJECT_ID(@vcTableName)))

             

              IF @ntOption = 2

                   SELECT @vcDropQuey

              ELSE IF @ntOption = 3

                   EXECUTE(@vcDropQuey) --Dropping all the fk constraint on table

             

          END

         

     END TRY

     BEGIN CATCH

         

          SELECT

              ERROR_MESSAGE() AS ErrMsg,

              ERROR_LINE() AS ErrLine 

     END CATCH

    

END

To test it:

I am creating few table with foreign key constraints:

CREATE TABLE tblParent(

     ntID  BIGINT PRIMARY KEY

)

CREATE TABLE tblChild(

     ntID  BIGINT PRIMARY KEY REFERENCES tblParent

)

CREATE TABLE tblSubChild(

     ntID1  BIGINT PRIMARY KEY REFERENCES tblParent,

     ntID2  BIGINT REFERENCES tblChild

)

Now I would like drop the table tblParent using stored procedure.

Using option 0:

EXECUTE dbo.uspForeceDrop 'tblParent'

Or

EXECUTE dbo.uspForeceDrop 'tblParent',0

Output:

DROP TABLE [dbo].[tblSubChild];

DROP TABLE [dbo].[tblChild];

DROP TABLE [dbo].[tblParent]

To drop it you have to manually execute this script.

Using option 1:

EXECUTE dbo.uspForeceDrop 'tblParent',1

It will drop table tblParent and all referenced tables.

Using option 2:

EXECUTE dbo.uspForeceDrop 'tblParent',2

Output:

ALTER TABLE [dbo].[tblChild]

DROP CONSTRAINT [FK__tblChild__ntID__5B988E2F];

ALTER TABLE [dbo].[tblSubChild]

DROP CONSTRAINT [FK__tblSubChi__ntID1__605D434C];

DROP TABLE [dbo].[tblParent]

Now you have to manually execute above sql scrip to drop all foreign key constraints on the table tblContent and the table tblContent.

Using option 3:

EXECUTE dbo.uspForeceDrop 'tblParent',3

It will drop all the foreign key constraints on the table tblContent and the table tblContent.