How to Drop a SQL Server Table with Cascading Constraints?-Mysql Tutorial-php.cn

How to Drop a SQL Server Table with Cascading Constraints?

Barbara Streisand

Release： 2024-12-29 10:52:09

Original

464 people have browsed it

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:

Open SQL Server Management Studio.
Navigate to: Tools > Options > SQL Server Object Explorer > Scripting
Enable the "Generate script for dependent objects" checkbox.
Right-click on the table you want to drop.
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:

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

Copy after login

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

1	`EXEC` `DropTableCascade` `'YourTableName'`

Copy after login

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!