/*=============================================
 Modified By:
 Modified Date:
 Description:
=============================================
 Created By:	Andrew Butenko
 Create date:	12/07/2019
 Description:	create temp table with PK IDENTITY and replace current table
 Example:
	exec [dbo].[RecoverPK] @TableName='Employees'
	exec [dbo].[RecoverPK] @TableName='Clients'
	exec [dbo].[RecoverPK] @TableName='My Documents'
=============================================*/
CREATE PROC [dbo].[RecoverPK]
(
	@TableName sysname,
	@Suffix sysname='_tmp',
	@IdName sysname=N'ID',
	@SchemaName sysname='dbo'
) AS
BEGIN
	DECLARE @SQL NVARCHAR(MAX)='', @names NVARCHAR(MAX)= ','
		, @TempTableName sysname = '['+@SchemaName+'].['+@TableName+@Suffix+']'
		, @FullTableName sysname = '['+@SchemaName+'].['+@TableName+']'
	SET @SQL ='
IF EXISTS (SELECT TOP 1 1 FROM [sys].[tables] WHERE [object_id] = object_id('''+@TempTableName+''') )
	DROP TABLE '+@TempTableName+';

SELECT TOP 1 * INTO '+@TempTableName+' FROM '+@FullTableName+';
TRUNCATE TABLE '+@TempTableName+';
ALTER TABLE '+@TempTableName+' DROP COLUMN ['+@IdName+'];
ALTER TABLE '+@TempTableName+' ADD ['+@IdName+'] INT NOT NULL PRIMARY KEY IDENTITY(1,1);'
	--print @SQL
	EXEC sp_executesql @SQL

	SELECT @names +=',['+[name]+']'
	FROM [sys].[columns]
	WHERE [object_id] = object_id(@FullTableName)
		AND [name] != @IdName
	ORDER BY [COLUMN_ID]

	SET @names = REPLACE(@names,',,','')
	SET @SQL='
SET IDENTITY_INSERT '+@TempTableName+' ON;
INSERT INTO '+@TempTableName+' ([ID],'+@names+')
SELECT [ID],'+@names+' FROM '+@FullTableName+' WHERE [ID] IS NOT NULL;
SET IDENTITY_INSERT '+@TempTableName+' OFF;
DROP TABLE '+@FullTableName+';
'
	--print @SQL
	EXEC sp_executesql @SQL
	SET @SQL = @TempTableName

	EXECUTE sp_rename @TempTableName, @TableName, 'OBJECT'
END
GO