need some help, removing all constraints from table and relating FKs

[volk125]

I have a table that I need to remove the PK from however I need to remove all FKs from the rest of the tables in the database in order to do that. if someone has a script handy that does the job, please post. I really need it fast, no time to create one myself.

 

[jbenson001]

Why don't you create a diagram in EM? That will show you all related tables with the relationgships. Then you can delete them in there.
Also, don't ask for us to create something for you because you need it fast. That is your responsibility. You need to explain to your powers that be, that you need the time to do it. People are here to help, not do the work for you.

 

[volk125]

1st of all I needed a script that would be automated to be a part of a stored proc. 2nd I asked if maybe someone has the script handy and can share (not to write one for me). and 3rd if you don't wanna help then don't bother.

 

[jbenson001]

You would have found the answer if you did a little research first. There is a thing called Books On Line. The answer is to use Drop Constraint statments.

 

[jbenson001]

In an Alter Table statment

 

[volk125]

ALTER TABLE TableName DROP CONSTRAINT TableName_FK#

I know that. my issue was to automatically/dynamically find all FKs that relate to that table's PK and drop all FKs and then the PK.

And I already figured out (not exactly the way I wanted but it will work) the way to find all FKs in the database and drop them with a cursor. No help needed anymore. But thanks for your concern and unnecessary comments.

drop table #TConst
select isnull(B.name, A.name) as TableName, A.name as objectName, A.xtype, X.objectType into #TConst
from sysobjects A
left outer join sysobjects B on A.parent_obj=B.id
inner join
( select 1 as pos, 'U' as xtype, 'User table' as objectType union
select 2, 'PK', 'PRIMARY KEY constraint' union
select 3, 'F', 'FOREIGN KEY constraint'
) X on X.xtype = A.xtype
where 'U' in (A.xtype, B.xtype)
order by isnull(B.name, A.name), X.pos

select tablename,objectname from #TConst where xtype = 'f'

and then the cursor to go through the #Tconst table and drop each one.