How to extract table name into string variable

Question

Would you help me to extract table name into string variable in SQL Server 2017?

Problem:

In many procedures I have dynamic queries like this:

declare @tbl_name nvarchar(255) = 'm4_results'
if @columns_to_ins is not null
exec ('ALTER TABLE ' + @tbl_name + ' add ' + @columns_to_ins + ';')

After renaming the tables via Redgate/smart rename, all the 'live' tables (which are mentioned in the procedures directly, like select * from m4_results) are renamed.

However, table names in the string variables are not renamed because SQL Server doesn't see them as tables, but as strings.

After mass renaming the tables, many errors occur since string names are not renamed.

I would like to extract table name into variable and use it in the procedure, like
declare @tbl_name nvarchar(255) = (query for extract table without using table name as string)

A dictionary with such table names - is a way, but not optimal for me.

Answer 1

I have an idea, you could store your tablename records into an temp table, and add an identity column, when you use dynamic sql for tablename, you could use

DECLARE @SQL VARCHAR(MAX)
DECLARE @Count INT = 1
DECLARE @Table VARCHAR(20)
DECLARE @Column VARCHAR(20)

WHILE @COUNT <= (SELECT COUNT(*) FROM #temp) 
BEGIN
    select @table = TABLENAME FROM #temp WHERE id = @Count
    SELECT @Column = COLUMNNAME FROM #temp WHERE id = @Count

    SELECT @sql = 'Alter table '+ @table+' Add +' @column

PRINT @SQL

SET @Count = @Count + 1

I always use print at first to see if the result is correct, then change to exec

If you like my answer, please vote it, thank you.