SQL Server: Drop Table Cascade Equivalent

SQL Server: drop table cascade equivalent?

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

Equivalent for drop table table_name cascade constraints in SQL Server

As I know there is not one command in MsSql, but you can use INFORMATION_SCHEMA and dynamic SQL.

Something like this:

DECLARE @database nvarchar(50)
DECLARE @table nvarchar(50)

set @database = 'MyDatabase'
set @table = 'MyTable'

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

T-SQL: DROP Table cascade constraints equivalent?

NO, IN SSMS right click on the table, and select "script table as" then "drop to", then "new window", "file..." or "clipboard" and it will produce a script that will include all the necessary drops of FKs etc.

MSSQL: how to have multiple cascades with referencing the same table

By creating a trigger that first deletes the sub-level table records and then itself after using the instead of delete you can trigger the sub triggers like a cascading pattern executing in the right order.

This solves the issue of cascade by adding a little more logic. Also you may want to use the primary key instead of the ID here for correct record selections on bigger keys.

drop table table4

drop table table2

drop table table3

drop table table1

drop table table0



create table table0 (

   id integer not null primary key

)



create table table1 (

   id integer not null primary key

)



create table table2 (

   id integer not null primary key,

   table0_id integer not null,

   table1_id integer not null

)



create table table3 (

   id integer not null primary key,

   table1_id integer not null

)



create table table4 (

   id integer not null primary key,

   table2_id integer not null,

   table3_id integer not null

)



alter table table2 add constraint fk_table2_table0 foreign key (table0_id)

references table0 (id) on delete cascade on update no action



alter table table2 add constraint fk_table2_table1 foreign key (table1_id)

references table1 (id) on delete cascade on update no action



alter table table3 add constraint fk_table3_table1 foreign key (table1_id)

references table1(id) on delete no action on update no action



alter table table4 add constraint fk_table4_table2 foreign key (table2_id)

references table2(id) on delete cascade on update no action



alter table table4 add constraint fk_table4_table3 foreign key (table3_id)

references table3(id) on delete no action on update no action



GO

CREATE TRIGGER WhenRowFromTable3IsDeleted ON table3

   INSTEAD OF DELETE      

AS

BEGIN

   DELETE FROM table4 WHERE table3_id = (SELECT id FROM DELETED)

    DELETE FROM table3 WHERE id = (SELECT id FROM DELETED)

END

GO

CREATE TRIGGER WhenRowFromTable1IsDeleted ON table1

   INSTEAD OF DELETE      

AS

BEGIN

   DELETE FROM table3 WHERE table1_id = (SELECT id FROM DELETED)

    DELETE FROM table1 WHERE id = (SELECT id FROM DELETED)

END

GO



INSERT INTO table0 (id) VALUES (1)

INSERT INTO table1 (id) VALUES (1)

INSERT INTO table2 (id, table0_id, table1_id) VALUES (1, 1, 1)

INSERT INTO table3 (id, table1_id) VALUES (1, 1)

INSERT INTO table4 (id, table2_id, table3_id) VALUES (1,1,1)



DELETE FROM table3 WHERE id = 1



SELECT * FROM table1, table0

Create loop to drop tables with specific names/criteria

I would use "while" to iterate through the script texts extracted by the identity column in the prepared script table.

Declare @RowNumber As Int = 1
Declare @Cnt As Int
Declare @schema_name sysname = '<your schema>'
Declare @QueryText nVarChar(max)

Create Table #Script (ID Int Identity(1,1), QueryText nVarChar(max))

Insert Into #Script (QueryText)
Select Concat(N'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''',
                          Quotename(@schema_name), N'.',Quotename([name]),N''') AND type in (N''U'')) DROP TABLE ',
                          Quotename(@schema_name), N'.',Quotename([name]))
From sys.tables 
Where [name] Like 'TABLE_____NAME'

Select @Cnt = Count(*) From #Script

While (@RowNumber<=@Cnt)
    Begin
        Select @QueryText = QueryText From #Script Where ID=@RowNumber

        Exec sp_executesql @QueryText

        RAISERROR ('%s', 0, 1, @QueryText) WITH NOWAIT

        Select @RowNumber=@RowNumber+1
    End

or you can use string_agg to get a script to delete all tables and run it in one go without using a loop.

Declare @QueryText nVarChar(max)
Declare @max nVarChar(max) = ''
Declare @schema_name sysname = '<your schema>'

Select @QueryText=String_Agg(Concat(@max, N'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''',
                                           Quotename(@schema_name), N'.',Quotename([name]),N''') AND type in (N''U'')) DROP TABLE ',
                                           Quotename(@schema_name), N'.',Quotename([name])), ' ')
From sys.tables 
Where [name] Like 'TABLE_____NAME'

Execute sp_executesql @QueryText

Oracle drop constraint cascade equivalent in Sql Server

You are thinking of the CASCADE feature on FOREIGN KEY constraints, in relation to actual DELETE statements.

ALTER TABLE t2 add constraint FK_T2 foreign key(t_id) references t(id)
   ON DELETE CASCADE;

Dropping a constraint with CASCADE does not delete any rows.

DELETE deletes rows, if you have enabled ON DELETE CASCADE.

Dropping the constraint simply drops the constraint (and associated indexes and dependent constraints), not data rows. In SQL Server ALTER TABLE ... I am not aware that there is a "CASCADE" option as in Oracle.

From Oracle docs http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_3001.htm#i2103845 for the ALTER TABLE statement:

CASCADE Specify CASCADE if you want all other integrity constraints that depend on the dropped integrity constraint to be dropped as well.

SQL DROP TABLE foreign key constraint

No, this will not drop your table if there are indeed foreign keys referencing it.

To get all foreign key relationships referencing your table, you could use this SQL (if you're on SQL Server 2005 and up):

SELECT * 
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('Student')

and if there are any, with this statement here, you could create SQL statements to actually drop those FK relations:

SELECT 
    'ALTER TABLE [' +  OBJECT_SCHEMA_NAME(parent_object_id) +
    '].[' + OBJECT_NAME(parent_object_id) + 
    '] DROP CONSTRAINT [' + name + ']'
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('Student')

How to drop a table if it exists?

Is it correct to do the following?

IF EXISTS(SELECT *
          FROM   dbo.Scores)
  DROP TABLE dbo.Scores

No. That will drop the table only if it contains any rows (and will raise an error if the table does not exist).

Instead, for a permanent table you can use

IF OBJECT_ID('dbo.Scores', 'U') IS NOT NULL 
  DROP TABLE dbo.Scores; 

Or, for a temporary table you can use

IF OBJECT_ID('tempdb.dbo.#TempTableName', 'U') IS NOT NULL
  DROP TABLE #TempTableName; 

SQL Server 2016+ has a better way, using DROP TABLE IF EXISTS …. See the answer by @Jovan.

---

Related Topics