Change multiple table names in SQL Server using sp_rename dynamically

Question

I'm trying to change a series of table names in Microsoft SQL Server 2017 Standard edition using Microsoft SQL Server Management Studio 2018.

The code I've been transmitting is based on the following code:

use DatabaseX

declare @RunRW varchar(MAX)
declare @ArVC varchar(4)
declare @StartAr smallint
declare @SlutAr smallint
declare @Ar smallint

set @StartAr = 2000
set @SlutAr = 2018
set @Ar = @StartAr

while @Ar <= @SlutAr
begin
    set @ArVC = cast(@Ar as varchar)

    set @RunRW = '
        exec sp_rename ''TMP_Table_Name_' + @ArVC + ',''Table_Name_' + @ArVC + ''

    exec (@RunRW)

    set @Ar = @Ar + 1
end

I've tried different numbers of quotion marks in my exec sp_rename syntax. It seems that I can move around the failure by changing these. The example code above delivers the error message below:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'TMP_Table_Name_2000'.

I've also tried with four quotes instead of two.

Another try was to run the stored procedure dynamically like it is, without the @RunRW variable. That didn't work either.

What am I doing wrong/missing or is it not possible to run sp_rename with dynamic code?

Why do you *want* to do this? This seems like an XY Problem. — Thom A, Oct 21 '19 at 08:21
If you do need to inject (object) names **safely** into a dynamic statement as well, you need to be using `QUOTENAME`. — Thom A, Oct 21 '19 at 08:21
Also, whever you are declaring a datatype, **always** declare your length, scale or precision. You have `cast(@Ar as varchar)` in there, and not declaring the data type properly can result in unexpected results. — Thom A, Oct 21 '19 at 08:25

score 4 · Answer 1 · answered Oct 21 '19 at 08:22

You have missed the closing quote from both the table names. If you change:

set @RunRW = '
        exec sp_rename ''TMP_Table_Name_' + @ArVC + ',''Table_Name_' + @ArVC + ''

to be

set @RunRW = '
    exec sp_rename ''TMP_Table_Name_' + @ArVC + ''',''Table_Name_' + @ArVC + ''''

it will remove the error.

score 0 · Accepted Answer · answered Oct 21 '19 at 09:07

If you must do this, personally, I would do it this way. Using the sys objects you can effect only the objects that exist (which means if 2018 doesn't exist no error), and you properly quote the values. As you're only removing 'TMP_' from the start as well, you can use STUFF to replace those characters with '':

DECLARE @Start int = 2000,
        @End int = 2018;

DECLARE @SQL nvarchar(MAX);


SET @SQL = (SELECT STRING_AGG(N'EXEC sp_rename N' + QUOTENAME(s.[name] + N'.' + t.[name],'''') + N',N' + QUOTENAME(STUFF(t.[name],1,4,''),'''') + N';',NCHAR(13) + NCHAR(10))
            FROM sys.schemas s
                 JOIN sys.tables t ON s.schema_id = t.schema_id
            WHERE t.[name] LIKE N'TMP_Table_Name_[0-9][0-9][0-9][0-9]'
              AND TRY_CONVERT(int,RIGHT(t.[name],4)) BETWEEN @Start AND @End)

PRINT @SQL; --Your best friend.
EXEC sys.sp_executesql @SQL;

This'll likely also perform faster than a WHILE.

Thank you for the tip! It will be most helpful in the future, since I have quite a lot of tables with the prefix "tmp" which is generated when the table is created. And I would also like to thank you for the adjustments in my question. :) — MarJer, Oct 21 '19 at 10:28

Change multiple table names in SQL Server using sp_rename dynamically

2 Answers2