Drooping a Table irrespective of FK''s

Question

Post reply

Drooping a Table irrespective of FK''s

Dinesh Asanka

SSChampion

Points: 11058
More actions
February 27, 2005 at 11:28 pm

#65994

Hi,
Does anyone has a script to drop a tabel without giving consideration to the FK's

My Blog: http://dineshasanka.spaces.live.com/

Viewing 3 posts - 1 through 3 (of 3 total)

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

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

Is this what you want ?

It generates all needed statements without executing them !

-- creatie test case

if object_id('T_ParentMC') is null

begin

create table T_ParentMC (

col1 int identity(1,1) not null ,

col2 int not null,

col3 int not null,

col4 int not null,

col5 varchar(100) not null default ''

, CONSTRAINT PK_ParentMC PRIMARY KEY (col2,col3,col4)

&nbsp

print 'table T_ParentMC created'

end

go

if object_id('T_ChildMC') is null

begin

create table T_ChildMC (

col1 int identity(1,1) not null primary key,

col2FK1_1 int not null,

col3FK1_2 int not null,

col4FK1_3 int not null,

col5 varchar(100) not null default ''

, CONSTRAINT FK_C2P FOREIGN KEY (col2FK1_1, col3FK1_2, col4FK1_3)

REFERENCES T_ParentMC (col2,col3,col4)

&nbsp

print 'table T_ChildMC created'

end

go

-- creatie test end

Declare @ParentTbName varchar(128)

, @ParentTbOwner varchar(128)

select @ParentTbName = 'T_ParentMC', @ParentTbOwner = NULL

SET nocount on

create table #tmpPKeys(

TABLE_QUALIFIER sysname,

TABLE_OWNER sysname not null,

TABLE_NAME sysname not null,

COLUMN_NAME sysname not null,

KEY_SEQ smallint not null,

PK_NAME sysname null )

Create index ix#tmpPKeys on #tmpPKeys (TABLE_QUALIFIER, TABLE_OWNER,TABLE_NAME, KEY_SEQ)

-- Get PK-info

insert into #tmpPKeys

exec sp_pkeys @table_name = @ParentTbName

, @table_owner = @ParentTbOwner

-- [ , [ @table_qualifier = ] 'qualifier' ] -- DBName

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

-- [ , [ @pktable_qualifier = ] 'pktable_qualifier' ]

-- { , [ @fktable_name = ] 'fktable_name' }

-- [ , [ @fktable_owner = ] 'fktable_owner' ]

-- [ , [ @fktable_qualifier = ] 'fktable_qualifier' ]

print 'Begin transaction trxAlter_' + @ParentTbName

print ' '

print '-- Drop Referencing constraints'

select 'Alter Table [' + FKTABLE_OWNER + '].[' + FKTABLE_NAME + '] drop constraint [' + FK_NAME + ']' + char(13) + 'GO '

from #tmpFKeys

where Key_SEQ = 1

order by FKTABLE_OWNER, FKTABLE_NAME, FK_NAME

print '-- Drop Table '

Select 'drop table ' + isnull('[' + @ParentTbOwner + '].','') + '[' + @ParentTbName + ']' + char(13) + 'GO '

print 'Commit transaction trxAlter_' + @ParentTbName

-- cleanup

drop table #tmpPKeys

drop table #tmpFKeys

-- cleanup when testing is done

-- drop table T_ChildMC

-- drop table T_ParentMC

--

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

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 2

Thankx It works

My Blog: http://dineshasanka.spaces.live.com/