SQL Server：删除表级联等效吗？

Question

在 Oracle 中，要删除所有表和约束，您可以输入类似的内容

DROP TABLE myTable CASCADE CONSTRAINTS PURGE;

，这将完全删除表及其依赖项。 SQL服务器相当于什么？

Answer 1

在 SQL Server Management Studio 中，转到“选项”/“SQL Server 对象资源管理器”/“脚本”，然后启用“为依赖对象生成脚本”。然后右键单击表，脚本>下降到>新的查询窗口，它会为您生成它。

Answer 2

我不相信 SQL 有类似优雅的解决方案。您必须先删除所有相关约束，然后才能删除表。

幸运的是，这一切都存储在信息模式中，您可以访问它来获取攻击列表。

这篇博文应该能够满足您的需求：
http://weblogs.asp.net/jgalloway/archive/2006/04/12/ 442616.aspx

-- t-sql scriptlet to drop all constraints on a table
DECLARE @database nvarchar(50)
DECLARE @table nvarchar(50)

set @database = 'DatabaseName'
set @table = 'TableName'

DECLARE @sql nvarchar(255)
WHILE EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_catalog = @database and table_name = @table)
BEGIN
    select    @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + CONSTRAINT_NAME 
    from    INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    where    constraint_catalog = @database and 
            table_name = @table
    exec    sp_executesql @sql
END

Answer 3

这可能是一个糟糕的解决方案，但我发现它很快。它与 Vinnie 的答案类似，但 SQL 语句的产物是另一系列 SQL 语句，它将删除所有约束和表。

(
select
  'ALTER TABLE ' + tc.table_name + ' DROP CONSTRAINT ' + tc.constraint_name + ';'
from
  INFORMATION_SCHEMA.TABLES t
  ,INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
where
  t.table_name = tc.table_name
  and tc.constraint_name not like '%_pk'
  and tc.constraint_name not like 'pk_%'
  and t.table_catalog='<schema>'
) UNION (
select
  'DROP TABLE ' + t.table_name + ';'
from
  INFORMATION_SCHEMA.TABLES t
where
  t.table_catalog='<schema>'
)

Answer 4

这都是有趣的游戏，直到某些表引用您的表...

然后我必须更改提供的代码，如下所示：

CREATE PROCEDURE _cascadeConstraints @database nvarchar(30) = NULL, @table nvarchar(60) = NULL
as
DECLARE @sql nvarchar(255)
WHILE EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_catalog = @database and table_name = @table)
BEGIN
    select    @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + CONSTRAINT_NAME 
    from    INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    where    constraint_catalog = @database and 
            table_name = @table
    select @sql = 'ALTER TABLE ' + tc.TABLE_NAME + ' DROP CONSTRAINT ' + tc.CONSTRAINT_NAME
      from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc join
                  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc on
                   (rc.CONSTRAINT_CATALOG = tc.CONSTRAINT_CATALOG and
                    rc.CONSTRAINT_NAME = tc.CONSTRAINT_NAME) join
                  INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc_pk on
                   (tc_pk.CONSTRAINT_CATALOG = rc.CONSTRAINT_CATALOG and
                    tc_pk.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME)
     where tc.constraint_catalog = @database
       and tc_pk.TABLE_NAME = @table
    exec    sp_executesql @sql
END
go

Answer 5

最终我们要删除我们的表。
因此，我们可以简单地运行 2 个以下命令：

ALTER TABLE ... DROP CONSTRAINT ...

DROP TABLE ...

1>更改表PRJ_DETAILS 删除约束FK_PRJ_TYPE；

-- 表名和约束名是参数

2>放下桌子。

第一个删除约束及其与其表关联的名称
其次你可以删除桌子。

它对我有用而且也很容易。

Answer 6

我只需要删除外键

DECLARE @database nvarchar(50)
DECLARE @TABLE_NAME nvarchar(250)
DECLARE @CONSTRAINT_NAME nvarchar(250)
DECLARE @sql nvarchar(350)
set @database = 'XXX'


DECLARE db_cursor CURSOR FOR  
select TABLE_NAME, CONSTRAINT_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_catalog = @database and CONSTRAINT_TYPE='FOREIGN KEY'

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @TABLE_NAME, @CONSTRAINT_NAME  

WHILE @@FETCH_STATUS = 0  
BEGIN  

    select    @sql = 'ALTER TABLE ' + @TABLE_NAME + ' DROP CONSTRAINT ' + @CONSTRAINT_NAME 
    from    INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    where    constraint_catalog = @database and 
            table_name = @TABLE_NAME
    exec    sp_executesql @sql 

       FETCH NEXT FROM db_cursor INTO  @TABLE_NAME, @CONSTRAINT_NAME 
END  

CLOSE db_cursor  
DEALLOCATE db_cursor 

Answer 7

SET QUOTED_IDENTIFIER ON
GO

DROP PROCEDURE IF EXISTS dbo.DropFKConstraintsReferencingTable
GO

CREATE PROCEDURE dbo.DropFKConstraintsReferencingTable
(
    @tableName NVARCHAR(128)
)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX) = N'';
    
    -- Construct the dynamic SQL statement
    SELECT @sql += N'ALTER TABLE ' + QUOTENAME(OBJECT_NAME(f.parent_object_id)) + N' DROP CONSTRAINT ' + QUOTENAME(f.name) + N';' + CHAR(13) + CHAR(10)
    FROM sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id
    INNER JOIN sys.columns AS c ON fc.parent_object_id = c.object_id AND fc.parent_column_id = c.column_id
    WHERE OBJECT_NAME(f.referenced_object_id) = PARSENAME(@tableName,1);

    -- Execute the dynamic SQL
    EXEC sp_executesql @sql;
    -- Optional print of SQL statement, can be commented
    print @sql
   
END
GO

EXEC DropFKConstraintsReferencingTable '[myTable]'
-- EXEC DropFKConstraintsReferencingTable 'myTable'
-- EXEC DropFKConstraintsReferencingTable '"myTable"'

DROP TABLE IF EXISTS [myTable];
-- DROP TABLE IF EXISTS myTable;
-- DROP TABLE IF EXISTS "myTable";