MS-SQL 자동증가값 컬럼 초기화

고객의 요청으로 특정 스키마에서 자동증가값을 가지는 테이블을 테이블명을 기준으로 필터링하여 자동증가값 컬럼의 최대값을 기준으로 초기화를 을 실행합니다.

“테이블명 처리중 오류가 발생했습니다.” 정도로 간단하게나마 오류처리를 하였습니다.

declare @ident bigint,
        @tName varchar(255),
        @cName varchar(255),
        @sqlText nvarchar(1000),
        @sqlVariable nvarchar(100),
        @schmaName   nvarchar(100),
        @tableFilter nvarchar(100)

set     @schmaName = 'dbo'
set     @tableFilter = '%code%'
/*
    dbo 스키마에서 테이블명 code 가 포함된 모든 테이블 중
    자동증가값 컬럼이 있는 테이블에 대하여
    자동증가값 컬럼의 최대값을 기준으로 초기화를 을 실행합니다.
*/
declare c_identReset Cursor for
SELECT  B.name + '.' +  A.name as tableName,
        columnName
FROM    sys.tables AS A
        INNER JOIN sys.schemas AS B
            ON  A.schema_id = B.schema_id
        inner join (
                    select  distinct
                            object_id,
                            name as columnName
                    from    sys.columns
                    where   is_identity = 1
                    ) as c
            on  c.object_id = A.object_id
WHERE   A.name LIKE @tableFilter
and     B.name = @schmaName
order by A.name  

open c_identReset

fetch next from c_identReset into @tName, @cName

while @@FETCH_STATUS = 0
    begin

        set @sqlVariable = N'@ident bigint'
        set @sqlText = N'
                            begin try
                                select @ident = isnull(max(' + @cName + '), 0) from ' + @tName + N'

                                -- select @ident as maxIdent

                                DBCC CHECKIDENT ( ''' + @tName + N''', RESEED, @ident) WITH NO_INFOMSGS
                            end try
                            begin catch
                                raiserror(''' + @tName + ' 처리중 오류가 발생했습니다'', 16, 1)
                            end catch
                            ' 

        --print @sqlText

        exec sp_executesql @sqlText, @sqlVariable, @ident = 0

        fetch next from c_identReset into @tName, @cName
    end
close c_identReset
deallocate c_identReset