7

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?

chris
  • 36,094
  • 53
  • 157
  • 237

2 Answers2

6

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
Community
  • 1
  • 1
Tony Hopkinson
  • 20,172
  • 3
  • 31
  • 39
3

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.

KM.
  • 101,727
  • 34
  • 178
  • 212
  • That only generated 'drop table [table]' . You have to ensure that in the options you enable scripting dependent elements then it works – missaghi Mar 30 '11 at 15:42
  • This is also not a viable solution if you need to generate a script that other people can use as there's no guarantee that the constraint names will be identical across multiple systems. – Chris Rasys Mar 10 '12 at 00:15