Use stored procedure to update an ID in all tables containing a particular column

Transact-SQL
1

I need to update a particular "tutor ID" in all tables that contain a column with a name like 'tutor%'. As this will be a repetitive task, I want to create it as a stored procedure for future use. I have very limited experience with SPs. I've gotten as far as declaring a temp table which will return all the table names that meet my criteria, but I would appreciate your help on writing the next part of the procedure that will go through the tables and change the tutor id according to the parameters I pass

Thanks in advance

CREATE PROCEDURE [dbo].[ChangeTutorId] 
	
	@oldId int, 
	@newId int
AS
BEGIN
	    	SET NOCOUNT ON;

     	declare @temp table
	
	(COLUMN_NAME nvarchar, TABLE_NAME nvarchar)

	insert into @temp

	SELECT COLUMN_NAME, TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%tutor%' and TABLE_NAME like 'tbl%'
2 
DECLARE db_cursor CURSOR FOR  
SELECT COLUMN_NAME, TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%tutor%' and TABLE_NAME like 'tbl%'

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @column_name , @table_name

WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @query = 'update ' + @table_name + '  set '+@column_name+'=' + cast( @newId as varchar(50)) + ' where '+ @column_name + '=' + cast( @oldId as varchar(50)) 

         EXECUTE sp_executesql @query 

       FETCH NEXT FROM db_cursor INTO @column_name , @table_name
END   

CLOSE db_cursor   
DEALLOCATE db_cursor
1 Like
3

Thank you so much, this is great. I just had to declare the query and it worked perfectly