SQL Server：删除表级联等效吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4858488/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server: drop table cascade equivalent?
提问by Sinaesthetic
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 服务器等价物？
采纳答案by Vinnie
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
回答by missaghi
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 对象资源管理器”/“脚本”，然后启用“为相关对象生成脚本”。然后右键单击该表，脚本 > 拖放到 > 新查询窗口，它会为您生成它。
回答by TJR
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>'
)
回答by Marco Polo
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
回答by Laxman G
Ultimately we are deleting our table. So we can simply run 2 following command:
最终我们将删除我们的表。所以我们可以简单地运行以下 2 个命令：
ALTER TABLE ... DROP CONSTRAINT ...
ALTER TABLE ... 删除约束 ...
DROP TABLE ...
删除表...
1> ALTER TABLE PRJ_DETAILSDROP CONSTRAINT FK_PRJ_TYPE;
1> ALTER TABLE PRJ_DETAILSDROP CONSTRAINT FK_PRJ_TYPE;
-- Table name and Constraint Name are the parameter
-- 表名和约束名是参数
2> DROP TABLE .
2> 删除表。
First drop constraint with its name associated with it table Second you can drop table.
第一个删除约束，其名称与它相关联表第二个你可以删除表。
It worked for me and its easy also.
它对我有用，也很容易。
回答by Carlos Morales Castro
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
