ReNumber_IDColumn_inTable

' Call SP Below as ....
'        [Schema1].[ReNumber_IDColumn_inTable] '{$Table$}' , '{$ColumnList$}', '{$TableTemp$}'
'        {$Table$}            Table name needed to renumber its ID column, ID Column has to be called 'ID'
'        {$ColumnList$}        Is list of columns in that table without ID column that is needed to be renumbered
'        {$TableTemp$}        Temporary table name, will be deleted at end of StoredProcedure
'        Example ...
'            [Schema1].[ReNumber_IDColumn_inTable] 'Langs' , 'LanguageID, LanguageName, LanguageDescription, DateAdded', 'TableNameTemp'

USE [databasename]
GO
/****** Object: StoredProcedure [Schema1].[ReNumber_IDColumn_inTable]    Script Date: 12/16/2023 5:37:12 PM ******/
DROP PROCEDURE [Schema1].[ReNumber_IDColumn_inTable]
GO
/****** Object: StoredProcedure [Schema1].[ReNumber_IDColumn_inTable]    Script Date: 12/16/2023 5:37:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        ANmar Amdeen with help from Bing AI
-- Created: 2023-11-14
-- Last updated: 2023-12-25        > > Adding index column and assign to catalog for Full-Text search ability
-- Description:    Reset numbers in ID column
--            create stored procedure with parameter as tablename and temptablename to renumber the ID column a unique ID to clear up the mess created by microsoft of ID column jumping 1000 numbers
--            I can help you with creating a stored procedure that takes a table name and a temporary table name as parameters, and renumbers the ID column of the table with a unique ID. Here is a possible code block that you can use:
--        EXEC renumber_id 'my_table', '#temp_table'
-- =============================================
CREATE PROCEDURE [Schema1].[ReNumber_IDColumn_inTable] @table_name VARCHAR(500), @ColumnList varchar(2000), @temp_table_name VARCHAR(500)
AS
BEGIN
Declare @SQL1 nvarchar(1000);

Set @SQL1 = ' DROP Table if exists ' + @temp_table_name -- Drop table if found
Exec sp_executesql @SQL1
Set @SQL1 = ' SELECT * INTO ' + @temp_table_name + ' FROM ' + @table_name + ' WHERE 1 = 0 ' -- Create a temporary table with the same structure as the original table
Exec sp_executesql @SQL1
Set @SQL1 = ' ALTER TABLE ' + @temp_table_name + ' DROP COLUMN ID ' -- Drop the old ID column from the temporary table
Exec sp_executesql @SQL1
Set @SQL1 = ' ALTER TABLE ' + @temp_table_name + ' ADD ID INT IDENTITY(1,1) ; ' -- Add a new column for the unique ID
Exec sp_executesql @SQL1
Set @SQL1 = ' INSERT INTO ' + @temp_table_name + ' SELECT ' + @ColumnList + ' FROM ' + @table_name -- Insert the data from the original table into the temporary table
Exec sp_executesql @SQL1
-- Rename the new ID column to id
-- Set @SQL1 = ' EXEC sp_rename ' + @temp_table_name + '".New_ID", "ID", "COLUMN" '
-- Exec sp_executesql @SQL1
Set @SQL1 = ' DROP TABLE ' + @table_name -- Drop the original table
Exec sp_executesql @SQL1
Set @SQL1 = ' SELECT ID, ' + @ColumnList + ' INTO ' + @table_name + ' FROM ' + @temp_table_name + ' ; ' -- Move columns back to Original table
Exec sp_executesql @SQL1

-- Adding index key 2023-12-25
Set @SQL1 = ' CREATE UNIQUE INDEX [PK_Codes_v24] ON ' + @table_name + ' (ID); '
Exec sp_executesql @SQL1

Set @SQL1 = ' CREATE FULLTEXT INDEX ON ' + @table_name + '(
    CodeFunctionName LANGUAGE ''English'',
    CodeTitle LANGUAGE ''English'',
    CodeDesc LANGUAGE ''English'',
    CodeParameters LANGUAGE ''English'',
    CodeTags LANGUAGE ''English'',
    CodeExamples LANGUAGE ''English''
    )KEY INDEX [PK_Codes_v24] ON ftCatalog; '
Exec sp_executesql @SQL1

Set @SQL1 = ' DROP TABLE ' + @temp_table_name -- Drop temp table
Exec sp_executesql @SQL1
END

GO