just to fix a few bugs
ALTER procedure [dbo].[MakeIndendity](@identityField varchar(50), @fieldsDeclare text,@fieldsSelect text,@tableName varchar(50))
as
Begin
EXEC('CREATE TABLE Tmp_tbl(' + @fieldsDeclare + ')')
SET IDENTITY_INSERT Tmp_tbl ON
EXEC('INSERT INTO Tmp_tbl (' + @fieldsSelect + ') SELECT ' + @fieldsSelect + ' FROM ' + @tableName + ' TABLOCKX')
SET IDENTITY_INSERT Tmp_tbl OFF
EXEC('DROP TABLE ' + @tableName)
EXEC('BEGIN TRY while (select 1 from ' + @tableName + ') = 1 begin if exists(select * from ' + @tableName + ') break else continue end END TRY BEGIN CATCH END CATCH ')
EXECUTE sp_rename N'Tmp_tbl', @tableName, 'OBJECT'
EXEC('ALTER TABLE ' + @tableName + ' WITH NOCHECK ADD CONSTRAINT [PK_' + @tableName + '] PRIMARY KEY CLUSTERED (' + @identityField + ') WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]')
End
Michael Evanchik
Microsoft MVP