Changing a Columns Identity Properties

Hi,
How do I change the identity properties of an existing column using
trans-sql?
Thanks
----
--
----
--Razak wrote:
> Hi,
> How do I change the identity properties of an existing column using
> trans-sql?
> Thanks
> ----
--
> ----[/
color]
What do you mean exactly? The seed? See DBCC CHECKIDENT.
David Gugick
Imceda Software
www.imceda.com|||I meant changing the property of the column to become an IDENTITY column.
This is because the column used to be an identity column, but someone has
messed up with the db and all the identity columns inside all tables has
lost their identity property. The columns are still there. Because of this,
every "INSERT ..." sql scripts to add new records generate error.
Therefore, I need to set back the IDENTITY prop for the id columns in every
table, but I need to do it using T-SQL since the db is in remote sql server.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:ea8pUeiGFHA.1500@.TK2MSFTNGP09.phx.gbl...
> Razak wrote:
> What do you mean exactly? The seed? See DBCC CHECKIDENT.
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Razak wrote:
> I meant changing the property of the column to become an IDENTITY
> column. This is because the column used to be an identity column, but
> someone has messed up with the db and all the identity columns inside
> all tables has lost their identity property. The columns are still
> there. Because of this, every "INSERT ..." sql scripts to add new
> records generate error.
> Therefore, I need to set back the IDENTITY prop for the id columns in
> every table, but I need to do it using T-SQL since the db is in
> remote sql server.
There is no supported SQL to add an identity attribute to an existing
column. Creating a new table and inserting the data between them is the
easiest way (in most cases).
You could use SQL EM, which will try and automate most of the this for
you, probably by creating a new table and then dropping the old one.
See this article for more information:
http://www.windowsitpro.com/Article...2080/22080.html
David Gugick
Imceda Software
www.imceda.com|||Thanks for your reply.. It seems like I will have to there and fix it
locally on the sever.
Thanks again
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:Oj8pn3jGFHA.2616@.tk2msftngp13.phx.gbl...
> Razak wrote:
> There is no supported SQL to add an identity attribute to an existing
> column. Creating a new table and inserting the data between them is the
> easiest way (in most cases).
> You could use SQL EM, which will try and automate most of the this for
> you, probably by creating a new table and then dropping the old one.
> See this article for more information:
> http://www.windowsitpro.com/Article...2080/22080.html
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Razak wrote:
> Thanks for your reply.. It seems like I will have to there and fix it
> locally on the sever.
You don't need to be on the server to fix it, unless I'm
misunderstanding what you're saying. You can connect through any query
tool and execute a custom script to make the change or possibly use SQL
EM (remotely is needed) to make the change. I would encourage you to
test all changes on a dev server first.
David Gugick
Imceda Software
www.imceda.com|||Creating a new copy of the table will break its relationship with other
tables since I need to maintain the values inside the supposedly id column.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OasMiRoGFHA.544@.TK2MSFTNGP12.phx.gbl...
> Razak wrote:
> You don't need to be on the server to fix it, unless I'm misunderstanding
> what you're saying. You can connect through any query tool and execute a
> custom script to make the change or possibly use SQL EM (remotely is
> needed) to make the change. I would encourage you to test all changes on a
> dev server first.
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Razak wrote:
> Creating a new copy of the table will break its relationship with
> other tables since I need to maintain the values inside the
> supposedly id column.
But that's the only solution to your problem AFAIK. If you try and make
the change using SQL EM on a dev server, you can run Profiler at the
same time and capture all the SQL SQLEM is uing to make the change. It
may shed some light on how to script this out yourself. I think the
article I referenced goes into this quite a bit.
David Gugick
Imceda Software
www.imceda.com