SQL Server Portal

In my last article, I had discussed how to convert a table from identity to sequence (manually). In this article, I would be sharing a script that I developed for one of my customers, to convert identity to sequence automatically. I presume that identity column belongs to integer data type family (small int, int, big int).
Note: Please test the script first on test database.

Given below is the example that we need to create, to understand the conversion:

Create table dbo.[Student]
(
[StudentID] int identity(1,1),
[StudentName] varchar(50)
CONSTRAINT PK_Student_StudentID PRIMARY KEY CLUSTERED
([StudentID] ASC)
)
GO
Insert into dbo.Student values ('Imran'),('Bob'),('Sandra')
GO
Select * from dbo.Student

Now, create the given below stored procedure to convert it automatically.

CREATE PROCEDURE Convert_Identity_To_Sequence_Proc
@Scheme_And_TableName nvarchar(Max)
AS

DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @OBJECTID AS BIGINT
DECLARE @IDENTITY_COLUMN_NAME AS NVARCHAR(MAX)
DECLARE @DATA_TYPE_NAME AS SYSNAME
DECLARE @CONSTRAINT_NAME AS NVARCHAR(MAX)
DECLARE @Max_identity_ID AS BIGINT
DECLARE @ParmDefinition AS NVARCHAR(MAX)
DECLARE @TABLE_NAME AS NVARCHAR(MAX)

--Pick up object ID of the table
SELECT @OBJECTID=OBJECT_ID(@Scheme_And_TableName)
--Seperate Table name from the schema
SET @TABLE_NAME =SUBSTRING(@Scheme_And_TableName,CHARINDEX('.',@Scheme_And_TableName)+1,LEN(@Scheme_And_TableName))
Print @TABLE_NAME

--Check if the table has an identity table
If (Select Count(*) from sys.identity_columns where object_id =@OBJECTID)=0
BEGIN
RAISERROR('Could not found the identity column in this table',16,1)
RETURN
END
Print @OBJECTID

-- Pick identity column name , contraint name and data type name from the table.
SELECT @IDENTITY_COLUMN_NAME=A.name,@CONSTRAINT_NAME=B.name, @DATA_TYPE_NAME=D.name FROM sys.columns A
INNER JOIN sys.types D ON A.system_type_id =D.system_type_id
LEFT JOIN sys.indexes B ON A.object_id =B.object_id
LEFT JOIN sys.index_columns C ON B.object_id =C.object_id
AND B.index_id =C.index_id
AND A.column_id =C.column_id
WHERE A.is_identity =1 And A.object_id =@OBJECTID

Print @IDENTITY_COLUMN_NAME
Print @CONSTRAINT_NAME
Print @DATA_TYPE_NAME

-- Add a new column in the table that does not have the IDENTITY property with the same data type
SET @SQL ='ALTER TABLE ' + @Scheme_And_TableName +' ADD ' +@IDENTITY_COLUMN_NAME + 'New ' + @DATA_TYPE_NAME + ' NULL'
Print @SQL
EXEC (@SQL)

-- Copy values from the old column and update into the new column
SET @SQL ='UPDATE ' + @Scheme_And_TableName +' SET ' + @IDENTITY_COLUMN_NAME + 'New' + ' =' + @IDENTITY_COLUMN_NAME
Print @SQL
EXEC (@SQL)

-- Drop the primary key constraint from the old identity column
SET @SQL ='ALTER TABLE ' + @Scheme_And_TableName + ' DROP CONSTRAINT ' + @CONSTRAINT_NAME
Print @SQL
EXEC (@SQL)

-- Drop the old identity column
SET @SQL =' ALTER TABLE ' + @Scheme_And_TableName + ' DROP COLUMN ' + @IDENTITY_COLUMN_NAME
Print @SQL
EXEC (@SQL)

-- Rename the new column to the old columns name
SET @SQL ='EXEC sp_rename ' + ''''+ @Scheme_And_TableName + '.'+ @IDENTITY_COLUMN_NAME+'New' + '''' + ',' + ''''+ @IDENTITY_COLUMN_NAME + '''' + ',' + '''COLUMN'''
Print @SQL
EXEC (@SQL)

-- Change the new column to NOT NULL
SET @SQL ='ALTER TABLE ' + @Scheme_And_TableName + ' ALTER COLUMN ' + @IDENTITY_COLUMN_NAME + ' ' + @DATA_TYPE_NAME + +' NOT NULL'
Print @SQL
EXEC (@SQL)

-- Add the unique primary key constraint again with the same name
IF @CONSTRAINT_NAME IS NOT NULL
BEGIN
SET @SQL ='ALTER TABLE ' + @Scheme_And_TableName + ' ADD CONSTRAINT ' + @CONSTRAINT_NAME +' PRIMARY KEY CLUSTERED (' + @IDENTITY_COLUMN_NAME +' ASC)'
Print @SQL
EXEC (@SQL)
END
ELSE
BEGIN
PRINT 'NO CONSTRAINT'
END
-- Get the highest current value from the column
-- to use it for sequence creation.
SET @SQL ='SELECT @Max_ID=MAX(' + @IDENTITY_COLUMN_NAME + ') FROM ' + @Scheme_And_TableName
SET @ParmDefinition = N'@Max_ID ' + @DATA_TYPE_NAME + ' OUTPUT'
Print @SQL
Print @ParmDefinition
EXECUTE sp_executesql @SQL, @ParmDefinition,@Max_ID=@Max_identity_ID OUTPUT;
Print @Max_identity_ID

-- Use the next Identity value as the START WITH VALUE;
SET @SQL ='CREATE SEQUENCE ' + @Scheme_And_TableName + 'Seq' +
' AS ' + @DATA_TYPE_NAME +
' START WITH ' + try_Convert(varchar(max),@Max_identity_ID+1) +
' INCREMENT BY 1 ' ;
Print @SQL
EXEC (@SQL)

-- Add a default value of sequence to the column
SET @SQL ='ALTER TABLE ' + @Scheme_And_TableName + ' ADD CONSTRAINT Def' + @TABLE_NAME + ' DEFAULT (NEXT VALUE FOR ' + @Scheme_And_TableName + 'Seq)
FOR ' + @IDENTITY_COLUMN_NAME;
Print @SQL
EXEC (@SQL)

GO
--Execute the stored procedure and provide schema and table name as a parameter.
EXEC Convert_Identity_To_Sequence_Proc 'dbo.Student'

Insert few records and check whether the table is properly converted from identity to sequence or not.

Insert into Student (StudentName) Values ('Mark')
Insert into Student (StudentName) Values ('Peter')
Select * from dbo.Student
--OUTPUT

Reference : MSDN