Modify a PRIMARY KEY constraint 2

[slaver]

Is there an easy way to modify a PRIMARY KEY constraint with query analyzer, for ALL the PRIMARY KEYS in ALL the tables in a SQL 2000 database.

Basically I need to run a script that will 'turn off' all the IDENTITY SEEDS of all the PRIMARY KEYS, and then one to turn them all back on.

I need the IDENTITY SEEDS turned off durning our Data transfer and then back on after we the transfer.

CAN ANYONE HELP ???

thanks

 

[essa2000]

Dear

To insert data in that table which is set to Identity filed you have to SET IDENTITY_INSERT of this table to ON and after insert you have set it back to OFF. Using this technique I think you can generate a Script which will SET IDENTITY_INSERT to ON and then perform your tranfer and then set it back to OFF using the another scripts.

You can use this Query to Generate the script which will SET it to ON or OFF.

TO SET it to ON

Select 'SET IDENTITY_INSERT ' + [Name] + ' ON' from dbo.sysobjects where Xtype = 'U'

TO SET it to OFF

Select 'SET IDENTITY_INSERT ' + [Name] + ' OFF' from sysobjects where Xtype = 'U'

when you run this Query it will give you the script which you can copy from the result pane in to Query pane and run it then it will set IDENTITY_INSERT of all the table to ON and after transfer you can run second Script which will turn it OFF.

I did not chcek it but I hope it will work for you.

Regards
Muhammad Essa Mughal