CODEWITHSUNDEEP
sqlsql-servert-sql
user9393635
user9393635

Reputation: 1429

what's the easiest way to dynamically/programatically drop tables sequentially based on their dependencies?

I need to drop a set of tables in a database dynamically/programatically. This will need to be done in sequence to avoid contraint errors. For example, a UserRole table will need to be dropped before a User table. I'm sure this can be done but I'm not sure what the easiest way would be to do this.

Upvotes: 1

Views: 285

Answers (1)

Hadi Ardebili
Hadi Ardebili

Reputation: 109

You don't need to drop all related tables to delete the table you want, just delete the Constraints that make the relation between 2 tables. This code will delete the constraints and then drop the table you want.

  DECLARE @Statement VARCHAR(300);
  DECLARE @TableName VARCHAR(300) = 'del';
  DECLARE @Schema VARCHAR(300) = 'dbo';
  DECLARE @DelStatement nvarchar (100);

  -- Cursor to generate ALTER TABLE DROP CONSTRAINT statements  
  DECLARE cur CURSOR FOR
  SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.' + 
  OBJECT_NAME(parent_object_id) +
                ' DROP CONSTRAINT ' + name
  FROM sys.foreign_keys 
  WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = @Schema AND 
            OBJECT_NAME(referenced_object_id) = @TableName;

  OPEN cur;
  FETCH cur INTO @Statement;

  -- Drop each found foreign key constraint 
  WHILE @@FETCH_STATUS = 0
  BEGIN
   exec(@Statement);
   FETCH cur INTO @Statement;
 END

 CLOSE cur;
 DEALLOCATE cur;
 -- Drop the table
SET @DelStatement = 'drop table '+@Schema+'.'+@TableName
exec(@DelStatement)

If you have a list of the tables you want to delete you could put this inside another cursor and it will run for each table.

Upvotes: 1

Related Questions