Binaryworld - Stored procedure to set/reset columns's identity property ... [ SQL Server -> Stored Procedure ]

You can use ALTER TABLE statement to add/remove column and to change some attributes of a column but there are some limitations with ALTER TABLE statement. You can not use ALTER TABLE to set a column as an identity column (Auto Increment field). You can ADD a new column with IDENTITY property but can not modify existing column's IDENTITY property using ALTER TABLE statement. Here is the stored procedure which is the best way to set and reset identity property of a field.

Click here to copy the following block

/*

////////////////////////////////////////
Use the following script to run the demo
////////////////////////////////////////

SET NOCOUNT ON

if object_id('tbl1') is not null
drop table tbl1

create table tbl1
(
cid int
,c1 char(5)
,c2 char(5)
)

Insert into tbl1 Values(8,'aa','aaasd')
Insert into tbl1 Values(5,'sdf','dfg')
Insert into tbl1 Values(10,'dfsd','dfg')

exec sp_SetIdentity 'tbl1',1,'cid' --//Set identity on cid column

Insert into tbl1 Values('dfsd','dfg')

exec sp_SetIdentity 'tbl1',0 --//Remove identity on cid column

select * from tbl1

exec sp_help tbl1

drop table tbl1

*/

create proc sp_SetIdentity
@tblname varchar(128) ='mytable'
,@set bit=0 --//0=Remove identity, 1= set identity
,@colname varchar(128)=NULL --//column name to set/reset identity
as

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET ANSI_WARNINGS OFF

declare @tmpcolname varchar(128)
declare @datatype varchar(128)
declare @tblname_bak varchar(128)
declare @CrLf char(2)

set @tblname_bak = @tblname + replace(cast(newid() as varchar(36)),'-','')
set @CrLf = char(13) + char(10)

--//Get Identity column name if exists
select @tmpcolname = COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @tblname
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1

--//in case of reset identity no need to supply col name
if (@colname is null)
set @colname=@tmpcolname

--//Get column datatype
select @datatype=DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @tblname AND COLUMN_NAME=@colname

--//Build column list without Identity column
DECLARE @cols varchar(8000), @delimiter char,@cmdSql varchar(8000)
SET @delimiter = ','

SELECT @cols = COALESCE(@cols + @delimiter,'') + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tblname
AND COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 0
AND COLUMN_NAME<>@colname

--//Set Identity
if @set=1 begin
if (@tmpcolname is not null) begin
if @tmpcolname = @colname
print 'Info : ' + @colname + ' is already identity column'
else
print 'Error : Table can have only one identity column'
end
else begin
--//This will create an emptry table with exisitng structure
set @cmdsql='select top 0 ' + @cols + ' into ' + @tblname_bak + ' from ' + @tblname
execute(@cmdsql)

--//This will add identity column
set @cmdsql= 'SET NOCOUNT ON' + @CrLf
set @cmdsql= @cmdsql + 'Alter table ' + @tblname_bak + @CrLf + ' Add ' + @colname + ' ' + @datatype + ' identity(1,1) not null'
execute(@cmdsql)
--print @cmdsql

--set @cmdsql='exec sp_help ' + @tblname_bak
--execute(@cmdsql)

--//This will copy data from source table to a backup table
set @cmdsql= 'SET NOCOUNT ON' + @CrLf
set @cmdsql= @cmdsql + 'SET IDENTITY_INSERT ' + @tblname_bak + ' ON' + @CrLf
set @cmdsql= @cmdsql + 'INSERT INTO ' + @tblname_bak + '('+ @cols + ',' + @colname +')' + @CrLf
+ 'SELECT ' + @cols + ',' + @colname + ' FROM ' + @tblname + @CrLf
set @cmdsql= @cmdsql + 'SET IDENTITY_INSERT ' + @tblname_bak + ' OFF'
execute(@cmdsql)

--//Finally drop old table and rename backup table
set @cmdsql= 'drop table ' + @tblname + @CrLf
set @cmdsql=@cmdsql + 'exec sp_rename ' + @tblname_bak + ',' + @tblname
execute(@cmdsql)

print @colname + ' is set to identity'
end
end
else begin
if (@tmpcolname <> @colname) begin
print 'Error : ' + @colname + ' is not an identity column'
end
else begin
--//This will create an emptry table with exisitng structure
set @cmdsql= 'SET NOCOUNT ON' + @CrLf
set @cmdsql= @cmdsql + 'select top 0 ' + @cols + ' into ' + @tblname_bak + ' from ' + @tblname
execute(@cmdsql)
--print @cmdsql

--//This will add identity column
set @cmdsql= 'SET NOCOUNT ON' + @CrLf
set @cmdsql= @cmdsql + 'Alter table ' + @tblname_bak + @CrLf + ' Add ' + @colname + ' ' + @datatype
execute(@cmdsql)

set @cmdsql= 'SET NOCOUNT ON' + @CrLf
set @cmdsql= @cmdsql + 'INSERT INTO ' + @tblname_bak + ' ('+ @cols + ',' + @colname +')' + @CrLf
+ 'SELECT ' + @cols + ',' + @colname + ' FROM ' + @tblname
execute(@cmdsql)

--//Finally drop old table and rename backup table
set @cmdsql= 'drop table ' + @tblname + @CrLf
set @cmdsql=@cmdsql + 'exec sp_rename ' + @tblname_bak + ',' + @tblname
execute(@cmdsql)

print @colname + ' is reset'
end
end
go