February 9, 2011 at 8:26 am
Hello -
I am seeking out a solution to a fairly serious problem. My database is needing to have all of it's Identity Columns removed, and I need to do this through TSQL, and not in SSMS.
I have researched this a bit, and the conclusion seems to be that there is not way to do it through TSQL. The closest thing I could find was the following, but it would not work (claiming that Ad hoc updates to system catalogs are not allowed...)
sp_configure 'allow update', 1
go
reconfigure with override
go
update syscolumns set colstat = 0 --turn off bit 1 which indicates identity column
where id = object_id('Table_Name') and name = 'Column_Name'
go
exec sp_configure 'allow update', 0
go
reconfigure with override
go
Thank you in advance for any and all solutions.
February 9, 2011 at 8:32 am
First of all, why would you want to drop all of the identity columns in the database? These are usually use as surrogate keys amd there may be other records that depend on them (I.e. foreign keys). The way you are changing the identity column is not removing them as you stated you wanted to do. You should lookup in BOL about droppping columns. But before you go dropping all of hte identity columns, I would suggest fully researching if this is even a good idea. i ahve a felling it isn't
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 9, 2011 at 8:48 am
I'm removing the Identity Columns because I was asked to - not because I want to, nor because I think it would be a good practice. Trust me - this is not by my own advising!
That being said, and understanding that this is a request to be performed on an existing db with data in the table in question, to which the column is needing to be modified, I am seeking out a way to do this without having to drop the table all together, and rebuild through the process of dumping into a temp tbl, and then recreating and reinserting.
If that is the only way to go, then I will go that route, but my question is to see if there is a way to achieve this without having to go that way. Is there a query of some sort in T-SQL that can achieve this?
Thank you
February 9, 2011 at 8:50 am
It sounds like you want to remove the identity property rather than the column itself? This can be done in tsql but like Mike said this sounds like a decision you will very quickly regret. What is the reason to drop the identity?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 9, 2011 at 8:57 am
It sounds like you are dead set on doing this. If you don't care about the order of columns you can do something like this.
drop table IdentityTest
go
CREATE TABLE [dbo].[IdentityTest](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DataField] [uniqueidentifier] NULL
) ON [PRIMARY]
GO
insert IdentityTest
select NEWID()
go 100
select * from identitytest
alter table IdentityTest
add TempID int
go
update IdentityTest set TempID = ID
go
alter table IdentityTest
drop column ID
go
EXECUTE sp_rename N'dbo.IdentityTest.TempID', N'ID', 'COLUMN'
go
I still say that this will decision will haunt you or whoever made such a mandate very quickly. Any processes that insert data to any of these tables will be rendered useless as soon as you are required to provide a value for a previous identity column. Given that identity columns are so frequently primary keys any inserts are now going to have to check for uniqueness prior to the insert too. At any rate, the code above should be a tool to open the can of worms that you have been told to open. Good luck!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 9, 2011 at 8:58 am
Correct - I simply need to remove the identity constraint from the column.
The customer requirement has changed, so needless to say, I am having to implement a very non-best practice to achieve this.
Any ideas on how I can do this through T-SQL?
February 9, 2011 at 9:00 am
I still say that this will decision will haunt you or whoever made such a mandate very quickly. Any processes that insert data to any of these tables will be rendered useless as soon as you are required to provide a value for a previous identity column. Given that identity columns are so frequently primary keys any inserts are now going to have to check for uniqueness prior to the insert too. At any rate, the code above should be a tool to open the can of worms that you have been told to open. Good luck!!!
No - just need to drop the contraint - not the whole table (that's what I am trying to avoid!).
If there is no way for me to alter the table to remove the constraint from the column which has it, and I have to drop and recreate the table, then I will, but one way or the other - I need to do this (again - I would NEVER want to do this, but I have no choice).
This is just how it goes when you are coming in mid-way...
February 9, 2011 at 9:03 am
*** Edited *** same example as Sean's
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 9, 2011 at 9:05 am
Hi Mike -
This is definitely better than doing the whole table, but still - is there a way to simply remove the constraint from the column via T-SQL, as opposed to droping the table/column and recreating?
My apologies - my post is not titled accurately, so please forgive me for any confusion.
It should be "Need to remove Identity column constraint via T-SQL"
February 9, 2011 at 9:07 am
ummm that is the same example i already posted. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 9, 2011 at 9:08 am
Sean Lange (2/9/2011)
ummm that is the same example i already posted. 😀
Sorry about that, I posted it before I got your update. I'll edit mine out
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 9, 2011 at 9:15 am
No worries. Just struck me as funny that we both basically wrote the exact same thing. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 9, 2011 at 9:16 am
Mike / Sean -
Thank you both. I guess there is no way to simply remove the column constraint then without having to copy the column/table to a temptbl, and then rebuild it?
February 9, 2011 at 9:16 am
Rich,
unfortunately, there is no way of removing the indentity characteristic from the column. Catalog updates are not supported
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 9, 2011 at 9:18 am
Rich Yarger (2/9/2011)
Mike / Sean -Thank you both. I guess there is no way to simply remove the column constraint then without having to copy the column/table to a temptbl, and then rebuild it?
That is correct, unless like both of our examples illustrated but the result is the column is now the last column in the table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply