SQL Server：删除表级联等效吗？ [英] SQL Server: drop table cascade equivalent?

问题描述

在oracle中，要删除所有表和约束，您将键入以下内容

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

DROP TABLE myTable CASCADE CONSTRAINTS PURGE;

，这将完全删除表及其依赖项。什么是SQL Server等效项？

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

推荐答案

我不认为SQL具有类似的优雅解决方案。

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

幸运的是，所有这些约束都存储在信息模式中，您可以访问它以获取表名。

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

此博客文章应该能够为您提供所需的信息：
http://weblogs.asp.net/jgalloway/archive/2006/04/12/442616.aspx

This blog post should be able to get you what you need: 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

这篇关于SQL Server：删除表级联等效吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！