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

Question

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.

possible duplicate of [SQL SERVER 2005: Drop all the tables, stored procedures, triggers, constriants and all the dependencies in one sql statement.](http://stackoverflow.com/questions/536350/sql-server-2005-drop-all-the-tables-stored-procedures-triggers-constriants-an) — wallyk, Apr 22 '11 at 18:38
Accepted answer of this [SO question](http://stackoverflow.com/questions/3812889/drop-cascade-in-sql-server) gives you direction. Though it is in different language. — Sanjeevakumar Hiremath, Apr 22 '11 at 18:40
@wallyk, it is not duplicate. The question there is to `delete everything`, this question is about `deleting everything dependent on one table`. — Sanjeevakumar Hiremath, Apr 22 '11 at 18:42

score 33 · Accepted Answer · edited May 16 '18 at 15:36

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

score 3 · Answer 2 · edited May 23 '17 at 11:53

3

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.

edited May 23 '17 at 11:53

Community

1
1

answered Apr 22 '11 at 18:53

Conrad Frix

51,984
12
96
155

Or maybe someone *more* lazy actually... :) – Andriy M Apr 22 '11 at 18:56
Looks like there is no one less lazy than you! I guess I will accept your answer then... – W3Max Apr 26 '11 at 18:57

score 2 · Answer 3 · answered Jun 27 '13 at 19:45

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

score 0 · Answer 4 · answered Aug 06 '20 at 13:50

In my case, I specifically wanted to drop a specified table and the tables that depend on that table. It wasn't useful to me to only drop the foreign key constraints that reference it. I wrote a stored procedure to do this

CREATE PROCEDURE DropDependentTables (
  @tableName NVARCHAR(64))
AS
-- Find and drop all tables that depend on @tableName
WHILE EXISTS(SELECT *
  FROM sys.foreign_keys
  WHERE OBJECT_NAME(referenced_object_id) = @tableName AND
    OBJECT_NAME(parent_object_id) != @tableName)
BEGIN
  DECLARE @dependentTableName NVARCHAR(64)
  SELECT TOP 1 @dependentTableName = OBJECT_NAME(parent_object_id)
    FROM sys.foreign_keys
    WHERE OBJECT_NAME(referenced_object_id) = @tableName AND
      OBJECT_NAME(parent_object_id) != @tableName

    EXEC DropDependentTables @dependentTableName
END

score 0 · Answer 5 · answered Sep 03 '20 at 05:36

I'm going to leave a late answer (after around 10 years). I hope you'll find it handy.

In our company, we use this script to properly delete database tables. For each table, we first drop the dependencies (REFERENTIAL_CONSTRAINTS) then delete the table itself.

USE [database-name]

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR

SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_SCHEMA + '].[' +  tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + '];'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME

OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql

WHILE (@@FETCH_STATUS = 0)
BEGIN
Exec sp_executesql @Sql
FETCH NEXT FROM @Cursor INTO @Sql
END

CLOSE @Cursor DEALLOCATE @Cursor
GO

EXEC sp_MSforeachtable 'DROP TABLE ?'
GO

The credit goes to a colleague of mine, Abolfazl Najafzade, for the script.

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

5 Answers5