-1

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Alex Ivanov
  • 657
  • 1
  • 8
  • 17
  • 3
    *"In many procedures I have dynamic queries like this"* And if they are like that every single one is **wide open** to injection attacks; you *really* need to fix them and quote your dynamic objects properly with `QUOTENAME`. – Thom A Jan 21 '21 at 09:25
  • 1
    *"But table names in the string variables are not renamed because SQL Server doesn't see them as tables, but as strings."* Not sure I follow here; the example you give has a literal string for the table's name, not a variable. Show us the code that *isn't* working, not the code that is. – Thom A Jan 21 '21 at 09:27
  • 1
    These are just strings, not table names. SQL Server doesn't know what's in them. You can query for the text of stored procedures and try to find specific string patterns, but SQL in general and T-SQL in particular is *terrible* at string manipulation. You'd need regular expressions at least, which aren't available in T-SQL. – Panagiotis Kanavos Jan 21 '21 at 09:27
  • Larnu, Panagiotis Kanavos, thank you for your comments. I've eddited the post. Additionaly I will add square brackets to avoid injection attacks. – Alex Ivanov Jan 21 '21 at 09:39
  • 1
    *"Additionaly I will add square brackets to avoid injection attacks"* if by that you mean you do `'...[' + @Variable + ']...'` that won't help, it's still wide open to injection, as I explain [here](https://wp.larnu.uk/an-in-depth-look-at-injecting/#sqlinjection). Again, use `QUOTENAME`. – Thom A Jan 21 '21 at 09:41
  • Larnu, thank you. Will use QUOTENAME. – Alex Ivanov Jan 21 '21 at 10:52

1 Answers1

0

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.

Jiacheng Gao
  • 365
  • 3
  • 9
  • Jiacheng Gao, thank you very much for your idea. Do I understand it correct, we should create a dictionary with table names and it's id's. Every time we use dynamic sql query, we get a table name from the dictionary. Hence, we need to update table name only once and not in all the procedures. – Alex Ivanov Jan 22 '21 at 06:14
  • 1
    That's correct, if you want to update a lot of tables with different column names, this way would make sure to go through all tables one by one. – Jiacheng Gao Jan 22 '21 at 14:44
  • Jiacheng Gao, your advise seems to be the best for me. Saying candidly, changing column names is not essential for me. Only tables. I've heard, there is another method, which doesn't require the renaming of the table names at all: 1) create storied procedure 2) make this procedure execute the only query: select top 1 * from table_name 3) via some built-in view in SSMS read table name which procedure uses 4) drop procedure. May be, someone has heard about it. – Alex Ivanov Jan 23 '21 at 17:21