News

Website is under construction

LogIn

T-SQL : Add identity to existing column in table

This script allows you to add identity to existing column for your table


CREATE Procedure sp_AddIdentityToTable
@tableName varchar(max),
@columnName varchar(max)
AS
Declare @newTableName varchar(max),
@constraintName varchar(max),
@insertColumnName varchar(max),
@dropfkCode varchar(max),
@createfkCode varchar(max),
@addfkCode varchar(max),
@refTable varchar(max),
@refColumnName varchar(max),
@dataType varchar(30),
@tempTableConstraint varchar(max),
@modifiedTempTableName varchar(max),
@primaryKeyColumns varchar(max)

Declare @foreignKeyTables Table(
tableName varchar(max),
columnName varchar(max),
foreignKeyName varchar(max)
)


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


SET @newTableName = '[' + @tableName + '_new_temp_table]'
SET @modifiedTempTableName = REPLACE(REPLACE(@newTableName,'[',''),']','')
SET @insertColumnName = NULL
SET @dropfkCode = ''
SET @createfkCode = ''
SET @addfkCode = ''





-- Check refTable and refColumnName to verify if table/column originate primary key
select distinct @refTable = object_name(referenced_object_id)
from sys.foreign_keys
where name in (
select constraint_name from information_schema.key_column_usage
where column_name = @columnName
and table_name = REPLACE(REPLACE(@tableName,'[',''),']','')
)
SELECT distinct @refColumnName = COL_NAME(ic.object_id,ic.column_id)
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic ON
i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1
and ic.object_id = object_id(@refTable)

--Get constraint and identity column
SELECT Distinct @constraintName = '[' + i.name + ']'
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1
and object_id(@tableName) = ic.object_id
and COL_NAME(ic.object_id,ic.column_id) = @columnName
--and COLUMNPROPERTY( OBJECT_ID(@tableName),COL_NAME(ic.object_id,ic.column_id),'IsIdentity')

-- Return if table does not exist
IF(OBJECT_ID(@tableName) IS NULL)
BEGIN
RETURN
END



--Return if column is already identity
IF (COLUMNPROPERTY( OBJECT_ID(@tableName),@columnName,'IsIdentity') = 1)
BEGIN
RETURN
END

--Return if column is not identity
IF(@columnName IS NULL)
BEGIN
RETURN
END


BEGIN TRY

-- Get Insert Column Names
SELECT @insertColumnName =
COALESCE(@insertColumnName + ', ','') + '[' + COLUMN_NAME + ']'
FROM information_schema.columns
WHERE TABLE_NAME = REPLACE(REPLACE(@tableName,']',''),'[','')

-- Get all primary key column names
SELECT @primaryKeyColumns =
COALESCE(@primaryKeyColumns + ', ','') +
'[' + COL_NAME(ic.object_id,ic.column_id) + ']'
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1
and object_id(@tableName) = ic.object_id

-- Get Column DataType
select distinct Top 1 @dataType = Data_Type
from information_schema.columns
where column_name = @columnName
and table_name = REPLACE(REPLACE(@tableName,'[',''),']','')

-- Store All constraint Table and name
Insert Into @foreignKeyTables (tableName, columnName, foreignKeyName)
SELECT '[' + OBJECT_NAME(f.parent_object_id) + ']',
'['+ COL_NAME(fc.parent_object_id, fc.parent_column_id)+ ']',
'[' + f.name + ']'
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.object_id = fc.constraint_object_id
WHERE COL_NAME(fc.referenced_object_id, fc.referenced_column_id) = @columnName
AND fc.referenced_object_id = object_id(@tableName)


-- Drop constraint

SELECT @dropfkCode =
@dropfkCode + 'ALTER TABLE [' + OBJECT_NAME(f.parent_object_id),
@dropfkCode =
@dropfkCode + '] DROP CONSTRAINT [' + f.name + '] '
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.object_id = fc.constraint_object_id
WHERE
COL_NAME(fc.referenced_object_id, fc.referenced_column_id) = @columnName
AND fc.referenced_object_id = object_id(@tableName)

if(LEN(@dropfkCode) > 0)
BEGIN
exec(@dropfkCode)
END
-- Generate temp table
IF(OBJECT_ID(@newTableName) IS NULL)
BEGIN
exec('Select Top 0 * into ' + @newTableName + ' FROM ['
+ @tableName + ']')
END

-- Generate key constraint for temp table
SELECT @addfkCode =
@addfkCode + 'ALTER TABLE ' + @tableName,
@addfkCode =
@addfkCode + ' ADD CONSTRAINT [' + f.name + '] ' +
'FOREIGN KEY ([' + COL_NAME(fc.parent_object_id, fc.parent_column_id) + ']) ' +
'REFERENCES [' + OBJECT_NAME(fc.referenced_object_id) + ']([' +
COL_NAME(fc.referenced_object_id, fc.referenced_column_id) + ']) '
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.object_id = fc.constraint_object_id
WHERE f.parent_object_id = object_id(@tableName)

-- Drop original constraint for table
exec('ALTER TABLE ' + @tableName +
' DROP CONSTRAINT ' + @constraintName)

-- Add Identity to temp table

exec('ALTER TABLE ' + @newTableName +
' DROP COLUMN ['+ @columnName + ']')
exec('ALTER TABLE ' + @newTableName +
' ADD [' + @columnName + '] ' + @dataType + ' IDENTITY(1ƹ) NOT NULL')
exec('ALTER TABLE ' + @newTableName +
' ADD CONSTRAINT '+ @constraintName +
' PRIMARY KEY CLUSTERED (' + @primaryKeyColumns + ')')


--Copy data to temp table
exec('SET IDENTITY_INSERT ' + @newTableName + ' ON'+
'Insert into ' + @newTableName + '(' + @insertColumnName + ') ' +
'SELECT ' + @insertColumnName + ' From [' + @tableName + ']
SET IDENTITY_INSERT ' + @newTableName + ' OFF')

-- DROP table
exec('DROP TABLE ' + @tableName)
-- rename table
exec sp_rename @newTableName, @tableName, 'OBJECT'

-- Add foreign key constraint to new table
IF(LEN(@addfkCode) > 0)
BEGIN
exec(@addfkCode)
END

-- Recreate constraint

SELECT @createfkCode =
@createfkCode + 'ALTER TABLE ' + tableName,
@createfkCode =
@createfkCode + ' ADD CONSTRAINT ' + foreignKeyName + ' FOREIGN KEY ('+ columnName + ') ' +
'REFERENCES [' + @tableName + '] ([' + @columnName + ']) '
FROM @foreignKeyTables
if(LEN(@createfkCode) > 0)
BEGIN
exec(@createfkCode)
END


END TRY
BEGIN CATCH

END CATCH


USAGE:
@tableName -- Name of table to add identity to
@columnName -- Name of column to change to identity
exec sp_AddIdentityToTable 'TableName', 'ColumnName'

March 17,2008 9:07 PM by rpgmaker


7 Comments


rpgmaker said:

  • Please feel free to post any comment regarding anything that need to be changed in the script above. Thanks

April 24,2008 10:00 AM 

Anonymous said:

  • Awesome procedure mate. I was struggling to get a solution for this problem. You really made my day. Thanks, keep going..

    Cheers :)

July 17,2008 10:57 AM 

Anonymous said:

  • Is it really work? i try 2 times and no column were altered to identity

    Ex:
    exec sp_AddIdentityToTable 'JDI_AUTODELETE', 'AUTODELETE_ID'

August 21,2008 11:35 AM 

rpgmaker said:

  • The reason it did not work was because the column you are trying to alternate to become an identity does not contain a constraint. Replacing this code

    --Return if column is not identity
    IF(@constraintName IS NULL OR @columnName IS NULL)
    BEGIN
    RETURN
    END

    With


    --Return if column is not identity
    IF(@columnName IS NULL)
    BEGIN
    RETURN
    END

    should fix the problem

August 21,2008 5:12 PM 

rpgmaker said:

  • Also Note that you will need to modify the code to your specifications.

August 21,2008 5:16 PM 

Anonymous said:

  • Thanks for answer me :)

    But still dont work, when i run the script for the first time, the column identity is not created and all my primary keys on that table disappear... is it normal? i think today i have more time to look carefully to ur sp... but thanks

August 22,2008 5:13 AM 

rpgmaker said:

  • Sorry about the primary keys disappearing, I guess i need to update the code because i only restore the foreign keys and not the primary key constraints.

August 22,2008 1:47 PM 

  Post Comment