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?
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.