To remove “Is Identity” setting in SQL Server: Not simple at 10:37 AM

Yesterday I found a seems-like-simple SQL problem: It is a simple task inside SQL Server Enterprise Manager (Management Studio), but there is no simple single SQL statement to set “Is Identity” to “no” for a table column. Because the setting is not a constraint, “alter table” statement does not work.

It turns out I have to create a same temporary column, copy all data to that new column, remove the original column, and rename the temporary column back to that original name. Another way is to create a temporary table without setting "Is Identity" and copy all data ... Oh my, such a “simple” work!

But fortunately there is a tip available to generate SQL script for database schema change: In SQL Server Enterprise Manager, when you change table schema, you can let Enterprise Manager generate the schema change script for you. The most left button of this tool bar is used to “Generate change script”:


Yesterday, I used it to generate a complex script to remove “Identity” setting for a column:

-- To remove Identity setting of the Id column
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.partner_attribute_name
  DROP CONSTRAINT DF_partner_attribute_name_last_update_date
GO
ALTER TABLE dbo.partner_attribute_name
  DROP CONSTRAINT DF_partner_attribute_name_creation_date
GO
-- Create a temp table
CREATE TABLE dbo.Tmp_partner_attribute_name
 (
 partner_attribute_name_id int NOT NULL,
 attribute_name varchar(50) NOT NULL,
 description varchar(200) NOT NULL,
 last_update_date datetime NOT NULL,
 creation_date datetime NOT NULL
 )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_partner_attribute_name ADD CONSTRAINT
  DF_partner_attribute_name_last_update_date 
  DEFAULT (getdate()) FOR last_update_date
GO
ALTER TABLE dbo.Tmp_partner_attribute_name ADD CONSTRAINT
  DF_partner_attribute_name_creation_date 
  DEFAULT (getdate()) FOR creation_date
GO
-- Copy all data to the temp table
IF EXISTS(SELECT * FROM dbo.partner_attribute_name)
  EXEC('INSERT INTO dbo.Tmp_partner_attribute_name
   (partner_attribute_name_id, attribute_name
    , description, last_update_date, creation_date)
    SELECT partner_attribute_name_id
     , attribute_name, description
     , last_update_date, creation_date 
    FROM dbo.partner_attribute_name 
    WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.partner_attribute_name
GO
EXECUTE sp_rename N'dbo.Tmp_partner_attribute_name'
    , N'partner_attribute_name', 'OBJECT' 
GO
ALTER TABLE dbo.partner_attribute_name ADD CONSTRAINT
    PK_partner_attribute_name PRIMARY KEY CLUSTERED 
    (
        partner_attribute_name_id
    ) ON [PRIMARY]
GO
COMMIT

Note: You should generate the script BEFORE you save the changes in EM. Otherwise, that button will be disabled.