How to Modify IDENTITY Primary Key Column Data Type


You need three steps to modfiy the identity primary column.

Step 1: drop the table primary key

  

declare @tablename NVARCHAR(256) /* sysname */ = N'dbo.test';
declare @sql NVARCHAR(2000);
SELECT @sql = N'ALTER TABLE ' + @tablename 
    + ' DROP CONSTRAINT ' + name + ';'
    FROM sys.all_objects
    WHERE [type] = 'PK' AND [parent_object_id] = OBJECT_ID(@tablename);

EXEC sp_executeSQL @sql;
 

Step 2: modify the column data type

  
alter table dbo.test
alter  column id bigint

 

Sept 3: Add the primary back to the column

  
alter table dbo.test
add primary key (id)

 

A sample:

  
create table dbo.test (id int identity(1,1) primary key,col int)

declare @tablename NVARCHAR(256) /* sysname */ = N'dbo.test';
declare @sql NVARCHAR(2000);
SELECT @sql = N'ALTER TABLE ' + @tablename 
    + ' DROP CONSTRAINT ' + name + ';'
    FROM sys.all_objects
    WHERE [type] = 'PK' AND [parent_object_id] = OBJECT_ID(@tablename);

EXEC sp_executeSQL @sql;

alter table dbo.test
alter  column id bigint

alter table dbo.test
add primary key (id)

drop table dbo.test
 

 

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s