T-SQL: DROP Table cascade constraints equivalent?

tsql cascade
chris picture chris · Feb 11, 2010 · Viewed 7.3k times · Source

In oracle, I can issue a DROP TABLE ... cascade constraints and it won't complain about FKs, etc.

Is there an equivalent in T-SQL?

Answer

Tony Hopkinson picture Tony Hopkinson · Jan 18, 2013

For those who got here in the hope of a more generally applicable answer

This will find the constraint, drop it, and then the column

Thanks and a vote to Tim Lentine How to find the name of a default constraint for the start.

Declare @sql VarChar(255)
Declare @tableName Varchar(255)
Declare @columnName VarChar(255)
Select @tableName = 'MyTableName'
Select @columnName = 'MyColumnName'
select @sql = o.[name] from sysobjects o 
inner join syscolumns c
on o.id = c.cdefault
inner join sysobjects t
on c.id = t.id
where o.xtype = 'd'
and t.name = @tableName
and c.name = @columnName

if @sql is not null
begin
  select @sql = 'Alter Table ' + @tableName + ' Drop Constraint ' + @sql + ' Alter Table ' + @tablename + ' Drop Column ' + @columnName
  exec(@sql)
end

Related questions

tsql script to add delete cascade to existing tables

is there a script that can be used to enable cascaded deletion for existing tables. Thanks.

Read More
Two foreign keys reference one table - ON UPDATE SET NULL doesn't work

I've got two foreign keys in a table. Let's assume that table is called News and has foreign keys updatedById and createdById, both of which point to userId in table Users. Now I want to set to NULL foreign keys …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More