dropping table with indentity referenced by FKs

Question

Post reply

dropping table with indentity referenced by FKs

mjschwenger

SSCarpal Tunnel

Points: 4019
More actions
June 1, 2005 at 8:33 pm

#167147

What we could do if we want to drop a table without touching the PK and referenced by it FKs? In Oracle we could do:"drop table tbl_name cascade;" and that will do...
What is the way in SQL server to do the same?
Many thanks,
MJ

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

you mean dropping the parent table will also drop all dependant tables ?

sqlserver does not support that. You'll have to build your drop-ddl yourself.

You can do that with a recurrent script based on combination of sp_keys and sp_fkeys .

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 2

I am not really sure what you mean with "[not] touching the PK and referenced by it FKs". You cannot drop a table if there are other tables with foreign keys referencing it. In that case you must first drop those foreign key constraints before you drop the table. I am not knowledgable about Oracle so I do not know exactly what that command does, but I assume it automatically drops all referencing foreign keys (which seems weird since the foreign keys are there for a reason). There is no similar command in SQL Server, you must do it manually.

Why would you want to drop a table that is referenced by others?

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

mjschwenger SSCarpal Tunnel Points: 4019 More actions · Answer 3

I'm doing conversion/upgrade involving a lot of old tables to be replaced by new ones or just deleted. It's a lot of time to find all FKs and then write statement to drop them before to drop or change the parent table...

Thanks a lot.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

In that case, I hope this can help out

TEST_TEST_TEST

declare @ParentTbName sysname

declare @ParentTbOwner sysname

Select @ParentTbName = 'T_Server' -- = your table to be dropped

, @ParentTbOwner = 'dbo'

create table #tmpFKeys

(PKTABLE_QUALIFIER sysname not null,

PKTABLE_OWNER sysname not null,

PKTABLE_NAME sysname not null,

PKCOLUMN_NAME sysname not null,

FKTABLE_QUALIFIER sysname not null,

FKTABLE_OWNER sysname not null,

FKTABLE_NAME sysname not null,

FKCOLUMN_NAME sysname not null,

KEY_SEQ smallint not null,

UPDATE_RULE smallint not null,

DELETE_RULE smallint not null,

FK_NAME sysname not null,

PK_NAME sysname not null,

DEFERRABILITY int not null)

Create index #tmpFKeys on #tmpFKeys (FK_NAME, KEY_SEQ)

-- Get FK-info (all dependant objects)

insert into #tmpFKeys

exec sp_fkeys @pktable_name = @ParentTbName

, @pktable_owner = @ParentTbOwner

-- select * from #tmpFKeys

-- select all constraints to be dropped

Select 'Alter table [' + FKTable_Owner + '].[' + FKTable_Name + '] drop constraint [' + FK_name + ']' + char(13) + 'GO'

from #tmpFKeys

-- drop your table

Print ' Drop table [' + @ParentTbOwner + '].[' + ParentTbName + ']'

drop table #tmpFKeys