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

CREATE Procedure sp_GenerateTableScript
@tableName varchar(max),
@newTableName varchar(max),
@executeScript bit
AS
Declare @script varchar(max),
@column_name varchar(max),
@column_default varchar(max),
@is_nullable varchar(max),
@data_type varchar(max),
@character_maximum_length varchar(max),
@constraintName varchar(max),
@primaryKeyColumns varchar(max),
@columnCount int,
@identitySeed int,
@identityInc int

Declare tableStruct CURSOR FOR
SELECT '[' + column_name + ']', column_default,
is_nullable, data_type, character_maximum_length
From information_schema.columns
where table_name = @tableName

SET @newTableName = REPLACE(REPLACE(@newTableName,'[',''), ']','')
SET @script = 'CREATE TABLE [' + @newTableName + ']('
SET @columnCount = (SELECT COUNT(*)
FROM information_schema.columns
where table_name = @tableName)


SELECT @identitySeed = IDENT_SEED(TABLE_NAME),
@identityInc = IDENT_INCR(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1
AND TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME = @tableName

SELECT Distinct @constraintName =
'[' + @newTableName + '_' +
REPLACE(i.name,@tableName,'') + ']'
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

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

OPEN tableStruct

FETCH NEXT FROM tableStruct
INTO @column_name, @column_default,
@is_nullable, @data_type, @character_maximum_length

WHILE(@@FETCH_STATUS = 0)
BEGIN
SET @script = @script + @column_name + ' ' + @data_type
IF(@character_maximum_length IS NOT NULL)
BEGIN
SET @script = @script +
' (' + CONVERT(varchar(4),@character_maximum_length) + ') '
END
IF(COLUMNPROPERTY(object_id(@tableName),
REPLACE(REPLACE(@column_name,'[',''),']',''),
'IsIdentity') = 1)
BEGIN
SET @script = @script + ' IDENTITY (' +
CONVERT(varchar(30), @identitySeed) + ',' +
CONVERT(varchar(30), @identityInc) + ') '
END
IF(@is_nullable = 'YES')
BEGIN
SET @script = @script + ' NULL '
END
ELSE
BEGIN
SET @script = @script + ' NOT NULL '
END
IF(@column_default IS NOT NULL)
BEGIN
SET @script = @script + 'DEFAULT ' + @column_default
END
IF(@columnCount > 1)
BEGIN
SET @script = @script + ', '
END
SET @columnCount = @columnCount - 1

FETCH NEXT FROM tableStruct
INTO @column_name, @column_default,
@is_nullable, @data_type, @character_maximum_length
END

CLOSE tableStruct
DEALLOCATE tableStruct

SET @script = @script + ') '

SET @script = @script + 'ALTER TABLE [' + @newTableName +
'] ADD CONSTRAINT '+ @constraintName +
' PRIMARY KEY CLUSTERED (' + @primaryKeyColumns + ') '


SELECT @script =
@script + 'ALTER TABLE [' + @newTableName,
@script =
@script + '] ADD CONSTRAINT [' + @newTableName + '_' +
COL_NAME(fc.parent_object_id, fc.parent_column_id) + '_FK] ' +
'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)


IF(@executeScript = 1)
BEGIN
EXEC(@script)
END
ELSE
BEGIN
SELECT @script
END