SQL Server：drop table cascade等效？

In oracle, to drop all tables and constraints you would type something like

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

DROP TABLE myTable CASCADE CONSTRAINTS PURGE;

and this would completely delete the tables and their dependencies. What's the SQL server equivalent??

这将完全删除表及其依赖项。什么是SQL服务器等价？

6 个解决方案

#1

I don't believe SQL has a similarly elegant solution. You have to drop any related constraints first before you can drop the table.

我不相信SQL有一个同样优雅的解决方案。在删除表之前，必须首先删除任何相关约束。

Fortunately, this is all stored in the information schema and you can access that to get your whack list.

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

This blog post should be able to get you what you need: http://weblogs.asp.net/jgalloway/archive/2006/04/12/442616.aspx

这篇博客文章应该能够满足您的需求：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

#2

In SQL Server Management Studio, go to Options / SQL Server Object Explorer / Scripting, and enable 'Generate script for dependent objects'. Then right click the table, script > drop to > new query window and it will generate it for you.

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

#3

This might be a horrible solution, but I find it's quick. It is similar to Vinnie's answer, but the product of the SQL statement is another series of SQL statements that will delete all constraints and tables.

这可能是一个可怕的解决方案，但我发现它很快。它类似于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>'
)

#4

Ultimately we are deleting our table. So we can simply run 2 following command:

最终我们正在删除我们的表格。所以我们可以简单地运行以下命令：

ALTER TABLE ... DROP CONSTRAINT ...

DROP TABLE ...

1> ALTER TABLE PRJ_DETAILS DROP CONSTRAINT FK_PRJ_TYPE;

1> ALTER TABLE PRJ_DETAILS DROP CONSTRAINT FK_PRJ_TYPE;

-- Table name and Constraint Name are the parameter

- 表名和约束名是参数

2> DROP TABLE .

2> DROP TABLE。

First drop constraint with its name associated with it table Second you can drop table.

第一个删除约束及其名称与其关联表第二个您可以删除表。

It worked for me and its easy also.

它对我有用，也很容易。

#5

This is all fun and games until some table references your table...

这是有趣的游戏，直到一些表引用你的表...

Then I must alter the code provided like so :

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

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

#6

I just need delete the foreign key

我只需要删除外键

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

#1