September 1, 2005 at 4:15 pm
How can I disable all the foreign reference key only for "NOT FOR Replication"
I am available to disable all the constraints by using the following query
sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
Amit Lohia
September 1, 2005 at 5:49 pm
thanks for the reply but it will drop the constraints. I just want to disable for not for replication. We can write scripts to do that (some what similar) but it comes with a cost
Amit Lohia
September 2, 2005 at 7:36 am
the NOT FOR REPLICATION option is used to handle incrementity IDENTITY values on primary keys.
here's my contribution to drop only foreign key contraints; i'm ignoring all other types of constraints , since those were previously provided.
you could edit this proc to disable the FK contraint instead of dropping it, or use it tp create the CREATE FK statements to go with the drop of FK
create Procedure Drop_All_Foreign_Keys
AS
/* Create a variable to hold dynamic SQL statement */
declare @Select_SQL as nvarchar(500)
/* Drop Foreign Key table if exists - this is temporary */
if exists (select * from sysobjects where id = object_id('Foreign_Key_Table') and sysstat & 0xf = 3)
drop table Foreign_Key_Table
/* Get foreign key info for one table */
select top 1
object_name(fkeyid) as foreign_key_table,
object_name(rkeyid) as primary_key_table,
object_name(constid) as [name]
Into Foreign_Key_Table
from sysforeignkeys
where OBJECTPROPERTY(rkeyid, N'IsUserTable') = 1
/* Drop foreign key found and get details on next until all have been dropped */
While (select count(*) from foreign_key_table) > 0
begin
set @Select_SQL = 'alter table '
+ (select foreign_key_table + ' drop ' + name from Foreign_Key_Table)
exec sp_executesql @Select_SQL
truncate table Foreign_Key_Table
Insert into Foreign_Key_Table
select top 1
object_name(fkeyid) as foreign_key_table,
object_name(rkeyid) as primary_key_table,
object_name(constid) as [name]
from sysforeignkeys
where OBJECTPROPERTY(rkeyid, N'IsUserTable') = 1
end
GO
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply