ReNumber_IDColumn_inTable

Reset numbers in ID column, with help from AI.
The StoredProcedure will move data in columns other than ID into temporary table, then adds ID column with new numbers, then move new data in all columns back to original table, and finally deletes temp table.
Columns need to be passed as argument since it is not smart enough to recognize ID column.
ID column will be named ID.
Works beautifully and will be using it as maintenance procedures.
Update 2023-12-25: Adding ability to restore index and full-text search catalog, columns are now hard-coded, will make them variable in next version.

CodeFunctionName
What is this?

Public

Tested

Original Work
' 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

@table_name VARCHAR(500), @ColumnList varchar(2000), @temp_table_name VARCHAR(500)

[Schema1].[ReNumber_IDColumn_inTable] 'Langs' , 'LanguageID, LanguageName, LanguageDescription, DateAdded', 'TableNameTemp'

Views 211

Downloads 98

CodeID
DB ID