删除表及其所有依赖项 (Microsoft SQL Server)

Dropping a table with all its dependencies (Microsoft SQL Server)

提问by W3Max

How can I drop a table with all its dependencies [SPs, Views, etc.] (Microsoft SQL Server) without knowing its dependencies upfront? I know I can display all dependencies in Mangement Studio but I'm searching for utility script that I could simply speficy an object and it would drop this object with all its dependencies.

如何在不预先知道其依赖项的情况下删除具有所有依赖项 [SP、视图等] (Microsoft SQL Server) 的表？我知道我可以在 Mangement Studio 中显示所有依赖项，但我正在寻找实用程序脚本，我可以简单地指定一个对象，它会删除该对象及其所有依赖项。

回答by JP Emvia

The best thing to do it is "Generate scripts for Drop"

最好的办法是“为 Drop 生成脚本”

Select Database -> Right Click -> Tasks -> Generate Scripts - will open wizard for generating scripts

选择数据库 -> 右键单击​​ -> 任务 -> 生成脚本 - 将打开生成脚本的向导

• Select the database -> next
• Set option 'Script to create' to true (want to create)
• Set option 'Script to Drop' to true (want to drop)
• Set option 'Generate script for dependent object' to true -> Next
• Select the Check box to select objects wish to create script
• Select the choice to write script (File, New window, Clipboard)

• 选择数据库->下一步
• 将选项“创建脚本”设置为 true（想要创建）
• 将选项 'Script to Drop' 设置为 true（想要删除）
• 将选项“为依赖对象生成脚本”设置为 true -> Next
• 选中复选框以选择希望创建脚本的对象
• 选择编写脚本的选项（文件、新窗口、剪贴板）

Execute the script

执行脚本

This way we can customize our script i.e., we can do scripting for selected objects of a database.

这样我们就可以自定义我们的脚本，即我们可以为数据库的选定对象编写脚本。

I hope this will help you!

我希望这能帮到您！

Best Wishes, JP

最好的祝福，JP

回答by Conrad Frix

You can use Sp_Dependsto find the dependencies. With that you can modify the script from this answerMaybe someone less lazy than me will do that for you.

您可以使用Sp_Depends来查找依赖项。有了这个，你可以修改这个答案的脚本也许有人比我更懒惰，会为你做这件事。

Note: Each object of course could have its own dependencies so you'll need to process them as well.

注意：当然，每个对象都可以有自己的依赖项，因此您也需要处理它们。

回答by JCO

Delete a SQL object using its schema-qualified name. For tables, the constraints are dropped first. Errors are ignored.

使用其模式限定名称删除 SQL 对象。对于表，首先删除约束。错误被忽略。

create procedure [dbo].[spDropObject] (@fullname nvarchar(520))
as
begin
    begin try
        declare @type nvarchar(5)
        declare @resolvedFullname nvarchar(520) 
        declare @resolvedName nvarchar(255) 
        set @type = null
        set @resolvedFullname = null
        set @resolvedName = null

        --find the object
        select 
        @type = o.[type] 
        ,@resolvedFullname = '[' + object_schema_name(o.id) +  '].[' + o.[name] + ']'
        ,@resolvedName = '[' + o.[name] + ']'
        from dbo.sysobjects o
        where id = object_id(@fullname)

        --PROCEDURE
        if(@type = 'P') 
        begin
            exec('drop procedure ' + @resolvedFullname);
            return;
        end

        --VIEW
        if(@type = 'V') 
        begin
            exec('drop view ' + @resolvedFullname);
            return;
        end

        --FUNCTION
        if(@type = 'FN' or @type = 'TF') 
        begin
            exec('drop function ' + @resolvedFullname);
            return;
        end

        --TRIGGER
        if(@type = 'TF') 
        begin
            exec('drop trigger ' + @resolvedFullname);
            return;
        end

        --CONSTRAINT
        if(@type = 'C' or @type = 'UQ' or @type = 'D' or @type = 'F' or @type = 'PK' or @type = 'K') 
        begin
            declare @fullTablename nvarchar(520);
            set @fullTablename = null

            --find the contraint's table
            select @fullTablename ='[' + object_schema_name(t.[object_id]) + '].[' + t.[Name] + ']'
            from sys.tables t
            join sys.schemas s on t.schema_id = s.schema_id
            where t.object_id = (select parent_obj from dbo.sysobjects where id = object_id(@resolvedFullname))

            exec('alter table ' + @fullTablename + ' drop constraint ' + @resolvedName);
            return; 
        end

        --TABLE (drop all constraints then drop the table)
        if(@type = 'U') 
        begin
            --find FK references to the table
            declare @fktab table([Name] nvarchar(255))
            insert @fktab 
            select 
            [Name] = '[' + object_name(fkc.[constraint_object_id]) + ']'
            /*
            ,[Parent] = '[' + object_schema_name(fkc.[parent_object_id]) +  '].[' + object_name(fkc.[parent_object_id]) + ']'
            ,[Ref] = '[' + object_schema_name(fkc.[referenced_object_id]) +  '].[' + object_name(fkc.[referenced_object_id]) + ']'
            */
            from sys.foreign_key_columns as fkc
            where referenced_object_id = object_id(@resolvedFullname)
            order by [Name]

            --iterate FKs
            while(1=1)
            begin
                declare @constraint nvarchar(255)
                set @constraint = null
                select top 1
                @constraint = [Name]
                from @fktab

                if(@constraint is not null)
                begin
                    --drop FK constraint
                    exec [dbo].[spDropObject] @constraint;
                    delete from @fktab where [Name] = @constraint --remove current record from working table
                end
                else break;
            end         

            --find constraints for table
            declare @constraintTab table ([Name] nvarchar(255));

            insert @constraintTab
            select [name] 
            from sys.objects 
            where parent_object_id = object_id(@resolvedFullname)
            order by [name]

            --iterate constraints
            while(1=1)
            begin
                set @constraint = null;
                select top 1 @constraint = [Name] from @constraintTab

                if(@constraint is not null)
                begin
                    --drop constraint
                    exec [dbo].[spDropObject] @constraint;
                    delete from @constraintTab where [Name] = @constraint --remove current record from working table
                end
                else break;
            end

            --drop table
            exec('drop table ' + @resolvedFullname);
            return;
        end
    end try
    begin catch
        declare @message nvarchar(max)
        set @message = error_message( ) ;
        print @message
    end catch
end