SQL Server: drop table cascade equivalent?

Question

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

DROP TABLE myTable CASCADE CONSTRAINTS PURGE;

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

Answer 1

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.

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

Answer 2

This is all fun and games until some table references your table...

Then I must alter the code provided like so :

CREATE PROCEDURE _cascadeConstraints @database nvarchar(30) = NULL, @table nvarchar(60) = NULL
as
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
    select @sql = 'ALTER TABLE ' + tc.TABLE_NAME + ' DROP CONSTRAINT ' + tc.CONSTRAINT_NAME
      from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc join
                  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc on
                   (rc.CONSTRAINT_CATALOG = tc.CONSTRAINT_CATALOG and
                    rc.CONSTRAINT_NAME = tc.CONSTRAINT_NAME) join
                  INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc_pk on
                   (tc_pk.CONSTRAINT_CATALOG = rc.CONSTRAINT_CATALOG and
                    tc_pk.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME)
     where tc.constraint_catalog = @database
       and tc_pk.TABLE_NAME = @table
    exec    sp_executesql @sql
END
go

Answer 3

Ultimately we are deleting our table. So we can simply run 2 following command:

ALTER TABLE ... DROP CONSTRAINT ...

DROP TABLE ...

1> ALTER TABLE PRJ_DETAILS DROP CONSTRAINT FK_PRJ_TYPE;

-- Table name and Constraint Name are the parameter

2> DROP TABLE .

First drop constraint with its name associated with it table Second you can drop table.

It worked for me and its easy also.

Answer 4

SET QUOTED_IDENTIFIER ON
GO

DROP PROCEDURE IF EXISTS dbo.DropFKConstraintsReferencingTable
GO

CREATE PROCEDURE dbo.DropFKConstraintsReferencingTable
(
    @tableName NVARCHAR(128)
)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX) = N'';
    
    -- Construct the dynamic SQL statement
    SELECT @sql += N'ALTER TABLE ' + QUOTENAME(OBJECT_NAME(f.parent_object_id)) + N' DROP CONSTRAINT ' + QUOTENAME(f.name) + N';' + CHAR(13) + CHAR(10)
    FROM sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id
    INNER JOIN sys.columns AS c ON fc.parent_object_id = c.object_id AND fc.parent_column_id = c.column_id
    WHERE OBJECT_NAME(f.referenced_object_id) = PARSENAME(@tableName,1);

    -- Execute the dynamic SQL
    EXEC sp_executesql @sql;
    -- Optional print of SQL statement, can be commented
    print @sql
   
END
GO

EXEC DropFKConstraintsReferencingTable '[myTable]'
-- EXEC DropFKConstraintsReferencingTable 'myTable'
-- EXEC DropFKConstraintsReferencingTable '"myTable"'

DROP TABLE IF EXISTS [myTable];
-- DROP TABLE IF EXISTS myTable;
-- DROP TABLE IF EXISTS "myTable";

Answer 5

In SQL Server Management Studio, go to Options / SQL Server Object Explorer / Scripting, and enable 'Generate script for dependent objects'. Then right click the table, script > drop to > new query window and it will generate it for you.

Answer 6

I just need delete the foreign key

DECLARE @database nvarchar(50)
DECLARE @TABLE_NAME nvarchar(250)
DECLARE @CONSTRAINT_NAME nvarchar(250)
DECLARE @sql nvarchar(350)
set @database = 'XXX'


DECLARE db_cursor CURSOR FOR  
select TABLE_NAME, CONSTRAINT_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_catalog = @database and CONSTRAINT_TYPE='FOREIGN KEY'

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @TABLE_NAME, @CONSTRAINT_NAME  

WHILE @@FETCH_STATUS = 0  
BEGIN  

    select    @sql = 'ALTER TABLE ' + @TABLE_NAME + ' DROP CONSTRAINT ' + @CONSTRAINT_NAME 
    from    INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    where    constraint_catalog = @database and 
            table_name = @TABLE_NAME
    exec    sp_executesql @sql 

       FETCH NEXT FROM db_cursor INTO  @TABLE_NAME, @CONSTRAINT_NAME 
END  

CLOSE db_cursor  
DEALLOCATE db_cursor 

Answer 7

This might be a horrible solution, but I find it's quick. It is similar to Vinnie's answer, but the product of the SQL statement is another series of SQL statements that will delete all constraints and tables.

(
select
  'ALTER TABLE ' + tc.table_name + ' DROP CONSTRAINT ' + tc.constraint_name + ';'
from
  INFORMATION_SCHEMA.TABLES t
  ,INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
where
  t.table_name = tc.table_name
  and tc.constraint_name not like '%_pk'
  and tc.constraint_name not like 'pk_%'
  and t.table_catalog='<schema>'
) UNION (
select
  'DROP TABLE ' + t.table_name + ';'
from
  INFORMATION_SCHEMA.TABLES t
where
  t.table_catalog='<schema>'
)