Is there any way to remove IDENTITY property on Table?

Is there any way to remove IDENTITY property on particular table? I tried removing IDENTITY property using Manangement studio, but this operation behind the scene use migration concept that is by creating tmp table and then populating with data; droping the orginal and renaming the tmp back to original.

Second, i want some kind of generic solution using certain system table like aya.sysobjects, sys.identitycolumn etc such a way that i should be able to remove the idenity property from all of the table accross a database.

Mandip

There are a couple of ways to 'remove' the IDENTITY 'property' of a field.

One is to :

make a duplicate table with or without the field that is IDENTITY -dependinp upon if you want to retain the current values or not,

copy the data from the old table to the new table,

DROP the old table,

RENAME the new table to the old table's Name.

|||

the first method i have already mentioned within my post... so i don't want... second sounds good.. i can use that methodolgy to create certain generic script to make changes across database.

Actually primary key is not concern.. as there is no primary key or index on those tables... in that particular DB...

thanks for help....

But still my question is is there any generic way/script to do this whole database level change....

thanks,

mandip

|||

But still my question is is there any generic way/script to do this whole database level change....

Nothing 'built-in'. You can write a script to do the 'deed'.

A query similar to this (SQL 2005) will give you a list of tables/columns that are IDENTITY.

SELECT

TableName = object_name( object_id ),

ColumnName = [name]

FROM sys.columns

WHERE is_identity = 1

Stuff the resultset into a #Temp table and cycle through it.

You'll have to use dyanmic SQL.