Setting Identity to No

[Dabase]

Hi,

Is it possible to set the identity of a column to "No" using a T-SQL statement?



Thanks
Dabase

 

[gmmastros]

Using SQL Server Management Studio, you can view the change script. I created a table with an identity column then used the management studio to remove the identity property. Here's the script it created.

BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_IdentityTest
	(
	Id int NOT NULL,
	OtherField varchar(10) NULL
	)  ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.IdentityTest)
	 EXEC('INSERT INTO dbo.Tmp_IdentityTest (Id, OtherField)
		SELECT Id, OtherField FROM dbo.IdentityTest WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.IdentityTest
GO
EXECUTE sp_rename N'dbo.Tmp_IdentityTest', N'IdentityTest', 'OBJECT' 
GO
COMMIT

1. Create a temporary holding table (without the identity property).
2. Copy the data in to the table.
3. Drop the original table
4. Rename the temporary table to the original table.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

And don't forget any indexes, constraints defaults etc etc
You will have to script those out

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLSister]

Or did you mean that you need to temporarily set it to off to load a bunch of historical records that already have values that you want to continue to use inthe new table?

Look up SET IDENTITY_INSERT in BOL for that. But do not use this capriciously. It can be very dangerous to use and should only be used for one-time movement of old data, not to avoid using the identity on a regular basis. Make sure you don;t try to insert any records that will be dupes of existing identity values when you use this. Also, I'm not sure but I believe that when you do this, the identity insert is off for all users, so make sure you do this when your regular users are not trying to add records themselves.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[SQLDenis]

>>I'm not sure but I believe that when you do this, the identity insert is off for all users, so make sure you do this when your regular users are not trying to add records themselves.
This is true it's on a table level, the other statements will fail since the are not supplying the id value

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLSister]

I thought so.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief