alter PROCEDURE [sp_table_setidentity] @Table varchar, @Schema varcha R (+) Asbegin DECLARE @vSQL nvarchar (max)-drop PK on [schoolid] SET @vSQL = N ' ALTER TABLE #Schema #.# table# DROP CONSTRAINT pk_#table#; ' Set @vSQL = replace (@vSQL, ' #Table # ', @Table) SET @vSQL = replace (@vSQL, ' #Schema # ', @Schema) print @vSql EXE C (@vSql)--Rebuild IDENTITY column SET @vSQL = N ' ALTER TABLE #Schema #. #Table # DROP column [SchoolID]; ALTER TABLE #Schema #. #Table # ADD [schoolid] int IDENTITY (1, 1) not NULL; ALTER TABLE [#Schema #]. [#Table #] ADD CONSTRAINT [pk_#table#] PRIMARY KEY CLUSTERED ([SchoolID] ASC) with (sort_in_tempdb = OFF, Ignore_dup_key = ON, ONLINE = OFF) on [PRIMARY]; ' Set @vSQL = replace (@vSQL, ' #Table # ', @Table) SET @vSQL = replace (@vSQL, ' #Schema # ', @Schema) print @vSql EXE C (@vSql) endexec [sp_table_setidentity] ' area_school ', ' dbo '
SQL modified self-growth ID