June 17, 2003 at 12:03 am
Hi,
I am porting a database from Oracle to Microsoft SQL server and I have a problem with the following statement in Oracle:
DROP TABLE myTable CASCADE CONSTRAINTS
How do it look like in SQL-Server?
Thx for any help,
/HakanM
June 18, 2003 at 2:53 am
if you are trying to drop a table being referenced by other table.
first manually drop the referencial constraints then only you can drop the table.
Edited by - nazim on 06/18/2003 02:53:40 AM
June 19, 2003 at 9:39 am
If you want a trouble-free way of dropping tables....
This sp drops all column constraints and defaults on a field, then drops the field.
The code is a bit messy (exec during a default cursor...) , but the SQL it's based on is sound.
You should be able to mess around with this so that it will drop all constraints on all fields in the table, then drop the table.
You can also look at sysobjects and sysreferences to see where the foreign keys to the table are - but the way they are joined is a bit fiddly - esp. if you have multiple fields in your foreign key.
CREATE proc DBAsp_DropColumn(@tabName sysname, @ColName sysname)
AS
declare @coname sysname
declare cr cursor for
select co.name
from sysobjects o
join syscolumns c
on o.id = c.id
join sysconstraints cn
on cn.colid = c.colid
and cn.id = c.id
join sysobjects co
on co.id = cn.constid
where o.type = 'U'
and o.name = @tabName
and c.name = @ColName
open cr
fetch from cr into @coname
while @@fetch_status = 0
begin
exec( 'alter table ' +@tabName + ' drop constraint ' + @coname )
fetch from cr into @coname
end
close cr
deallocate cr
declare @DefName sysname
select @DefName = o.name
from syscolumns c
join sysobjects o
on c.cdefault = o.id
join sysobjects o2
on c.id = o2.id
where c.name = @ColName and o2.name = @tabName and o2.type = 'U'
if not @DefName is null
begin
exec('alter table ' + @tabName + ' drop constraint ' + @DefName)
if @@error <> 0
begin
exec('sp_unbindefault ''' + @tabName + '.' + @ColName + '''')
exec('drop default ' + @DefName)
end
end
exec('alter table ' + @tabName + ' drop column ' + @ColName)
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply