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

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

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

You can use Sp_Depends to find the dependencies. With that you can modify the script from this answer Maybe someone less lazy than me will do that for you.

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

The best thing to do it is "Generate scripts for 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)

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