0

My question is that in Oracle we can use drop table table_name cascade constraints to drop a referenced table object. How can I achieve the same in SQL Server?

Kara
  • 6,115
  • 16
  • 50
  • 57
yayayokoho3
  • 1,209
  • 2
  • 16
  • 40

2 Answers2

0

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
ceth
  • 44,198
  • 62
  • 180
  • 289
  • thank you demas for the answer, but i wished if there could be a single line of tool for that. If any clues you find pls suggest me. – yayayokoho3 Jun 01 '11 at 09:31
  • You can create stored procedure from this code and use it as single line tool :) – ceth Jun 01 '11 at 09:33
  • Of course demas, your suggestion is nice. If we really do not have any single line SYNTAX from MSSQL, i conclude we've no option rather than what you suggested. Thank you again. – yayayokoho3 Jun 02 '11 at 04:15
0

There is an option to let Management Studio generate a script that alters/drops all constraints on dependent objects.

I found the desciption here SQL Server drop table cascade equivalent

Community
  • 1
  • 1
lvmeijer
  • 1,022
  • 13
  • 14