How to Drop a SQL Server Table with Cascading Constraints?

SQL Server: Drop Table with Cascading Constraints

In Oracle, the "CASCADE CONSTRAINTS PURGE" option in the DROP TABLE statement allows for the removal of a table and its dependent constraints and data.

The equivalent functionality in SQL Server is not available through a single command. However, there are two alternative ways to achieve the desired result:

Using Scripting Options:

1. Open SQL Server Management Studio.
2. Navigate to: Tools > Options > SQL Server Object Explorer > Scripting
3. Enable the "Generate script for dependent objects" checkbox.
4. Right-click on the table you want to drop.
5. Select "Script > Drop to > New Query Window."

This will generate a script that includes all dependent objects and will drop them in the correct order.

Using a Recursive Stored Procedure:

1. Create a recursive stored procedure that drops the table and all its dependent objects.
   e.g.:

CREATE PROC DropTableCascade (@TableName nvarchar(max))
AS
BEGIN
 IF OBJECT_ID(@TableName) IS NOT NULL
 BEGIN
  EXEC sp_MSForEachTable 'IF "{DB_NAME()}.dbo.' + name in (SELECT name FROM sysobjects WHERE type = ''U'' AND parent_id = OBJECT_ID(@TableName)) BEGIN PRINT ''Dropping table '' + ''{DB_NAME()}.dbo.'' + name PRINT ''DELETE FROM '' + ''{DB_NAME()}.dbo.'' + name + ''; DROP TABLE '' + ''{DB_NAME()}.dbo.'' + name + ''; END'
  DROP TABLE @TableName
 END
END

2. Call the stored procedure with the name of the table you want to drop:

EXEC DropTableCascade 'YourTableName'

The above is the detailed content of How to Drop a SQL Server Table with Cascading Constraints?. For more information, please follow other related articles on the PHP Chinese website!