Is there any way to remove all forgeinKey and primary key contriants from Database ?
A:
You could select rows from the dbo.sysobjects table with xtype in ('F', 'PK') and, for each row, generate the appropriate alter table statement. However, I would first ask, why do you want to do this? Constraints are very important for a number of different reasons.
Christian Hayter
2009-07-07 07:24:49
i want to remove all the contstraints and create all new constraints when ever i will remove pk then it will give me error that remove first fk that is main problme
KuldipMCA
2009-07-07 07:27:30
How about just dropping all the FKs then re-creating them?
Christian Hayter
2009-07-07 07:32:07
A:
You can find constraints listed in the sysobjects table:
SELECT * FROM sysobjects WHERE xtype IN ('F', 'PK')
Use a cursor to loop through the relevant records, build an SQL statement in a string that will remove each constraint, and EXEC() the statement.
richardtallent
2009-07-07 07:25:59
+1
A:
to build on Richard's code:
SELECT
'ALTER TABLE ' + object_name(parent_obj) + ' DROP CONSTRAINT ' + object_name(id),* FROM sysobjects WHERE xtype IN ('F', 'PK')
You can run the output in QA & watch your db churn as it turns your tables into heaps
Nick Kavadias
2009-07-07 07:35:42
Thanks like that we can do i have another option too-- select 'Alter Table ' + Table_Name + ' Drop Constraint ' + Constraint_Name -- from information_schema.TABLE_CONSTRAINTS where Constraint_type like 'F%'-- union-- select 'Alter Table ' + Table_Name + ' Drop Constraint ' + Constraint_Name -- from information_schema.TABLE_CONSTRAINTS where Constraint_type like 'P%'because i need to remove first forgeinkey and after primary key
KuldipMCA
2009-07-07 07:42:11
Done below is Query select 'Alter Table ' + Table_Name + ' Drop Constraint ' + Constraint_Namefrom information_schema.TABLE_CONSTRAINTS order by Constraint_type
KuldipMCA
2009-07-07 07:52:08