Disable Check constraints created by replication on all Identity keys
| set nocount on select case when c.is_disabled =1 then '----constraint '+c.name+'is been disabled 'else '' end +' '+ 'ALTER TABLE ['+schema_name(c.schema_id)+'].['+object_name(c.parent_object_id)+'] NOCHECK CONSTRAINT ['+c.name+']' --,object_name(c.parent_object_id), c.name, --schema_name(c.schema_id),c.is_not_for_replication from sys.check_constraints c join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cc on schema_name(c.schema_id) = cc.table_schema and object_name(c.parent_object_id) = cc.table_name join sys.identity_columns i ON c.parent_object_id = i.[object_id] where c.is_not_for_replication = 1 -------------------------------------------------------------------------------------------------------------- --Test Use Case create table x(xid int ,age int ) alter table x add constraint x_ck check NOT FOR REPLICATION (xid >10) create table y(yiid int identity(1,1),age int ) alter table y add constraint y_ck check NOT FOR REPLICATION (yiid >10) ---- below output shows only table y as table x doesnt have identity column select case when c.is_disabled =1 then '----constraint '+c.name+'is been disabled 'else '' end +' '+ 'ALTER TABLE ['+schema_name(c.schema_id)+'].['+object_name(c.parent_object_id)+'] NOCHECK CONSTRAINT ['+c.name+']' --,object_name(c.parent_object_id), c.name, --schema_name(c.schema_id),c.is_not_for_replication from sys.check_constraints c join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cc on schema_name(c.schema_id) = cc.table_schema and object_name(c.parent_object_id) = cc.table_name join sys.identity_columns i ON c.parent_object_id = i.[object_id] where c.is_not_for_replication = 1 -------------------------------------------------------------------------------------------------------------- insert into y select 10 select * from y ALTER TABLE y NOCHECK CONSTRAINT y_ck --or drop it ALTER TABLE y drop CONSTRAINT y_ck --insert into y select 10 select * from y |
No comments:
Post a Comment