Jan 5, 2014

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 +
              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.

No comments:

Post a Comment