dropping tables without removing Foreign key const

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

dropping tables without removing Foreign key const

Author

Topic

zion99
Posting Yak Master

141 Posts

Posted - 2008-10-18 : 07:40:20

Hi,

Is it possible to drop a table say table1 which is being referenced by other tables and later re-create table1 after making required changes?


Error: Could not drop object 'dbo.table1' because it is referenced by a FOREIGN KEY constraint.

something similar to set identity_insert on & off...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-18 : 07:44:48

Nope. you need to first drop the foreign key constraint before dropping the referenced table.

zion99
Posting Yak Master

141 Posts

Posted - 2008-10-18 : 07:51:52

ok, assume if i have the list of all foreign key constraints, i can generate a dynamic query & drop them.
But is it possible to have a list of the text for the foreign key constraints? something like sp_helptext.

I tried using sp_helptext for a constraint but it didn't work.
what i m thinking is, first get all such constrains, then get the text for the same, lastly build the constraints using the text

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-18 : 08:24:59

[code]select
T.table_name,TC.constraint_name
from
INFORMATION_SCHEMA.TABLES T left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
on T.table_name=TC.table_name
where
(TC.constraint_Type ='Foreign Key') and
T.table_name not in ('dtproperties','sysconstraints','syssegments')and
Table_type='BASE TABLE'
[/code]

Madhivanan

Failing to plan is Planning to fail

zion99
Posting Yak Master

141 Posts

Posted - 2008-10-18 : 08:49:06

Thnks madhivanan, but i require the text for Foreign key constraints.
Even OBJECT_DEFINITION doesn't give the text for Foreign key constraints...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-18 : 09:56:58

quote:
Originally posted by zion99

Thnks madhivanan, but i require the text for Foreign key constraints.
Even OBJECT_DEFINITION doesn't give the text for Foreign key constraints...

what do you mean by 'text for foreign key constraints'?

zion99
Posting Yak Master

141 Posts

Posted - 2008-10-18 : 10:11:50

when we use sp_helptext 'STORED PROC NAME', we get then entire text for the stored proc.
in a similar way, i want to get the text for the foreign key constraint

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-18 : 10:20:25

quote:
Originally posted by zion99

when we use sp_helptext 'STORED PROC NAME', we get then entire text for the stored proc.
in a similar way, i want to get the text for the foreign key constraint

for that you need to go to sql server management sudio and script out the foreign constraint from object explorer.

zion99
Posting Yak Master

141 Posts

Posted - 2008-10-18 : 10:43:38

i know that

, i was thinking if there was a way using sql query!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-19 : 12:44:29

What exactly changes are you going to make for this table so you have to drop it?

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-20 : 07:20:52

To find constraints on table:

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT' and TableName='YourTable'
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 08:25:16

quote:
Originally posted by darkdusky

To find constraints on table:

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT' and TableName='YourTable'
GO

or use

SELECT tc.CONSTRAINT_NAME,tc.CONSTRAINT_SCHEMA,tc.TABLE_NAME,tc.CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE tu
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
ON tc.CONSTRAINT_NAME=tu.CONSTRAINT_NAME
AND tc.TABLE_NAME=tu.TABLE_NAME
WHERE tc.TABLE_NAME='YourTable'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources