August 23, 2018 at 7:33 pm
I wanted to rename all tables (append '_Deleted' on each table) under specific schema in the same database. I have used the below scripts but I keep getting an error. Your help is much appreciated.
CREATE SCHEMA NJ;
CREATE SCHEMA AZ;
CREATE SCHEMA MN;
CREATE TABLE NJ.tbl (id INT, Name varchar(50));
CREATE TABLE AZ.tbl (id INT, Name varchar(50);
CREATE TABLE MN.tbl (id INT, Name varchar(50);
DECLARE @SQL NVARCHAR(max)=''
SELECT @SQL += 'exec sp_rename ' + TABLE_NAME + ',' + TABLE_NAME + '_Deleted'
FROM information_schema.tables
WHERE table_schema IN ('NJ', 'AZ', 'MN')
EXEC sp_executesql @SQL
Error Message: "Msg 15225, Level 11, State 1, Procedure sp_rename, Line 418 [Batch Start Line 21]
No item by the name of 'Test' could be found in the current database 'XXXX', given that @itemtype was input as '(null)'."
August 23, 2018 at 10:09 pm
If you're going to use sp_rename to rename tables other than dbo (or whatever your default schema is), you must specify the schema name in the first operand of sp_rename along with the table name as a 2 part named object'. You also need single quotes when doing so. Last but not least, you need to protect the renamed tables from being renamed again if another execution of the code occurs.
DECLARE @SQL NVARCHAR(max)=''
;
SELECT @SQL += 'exec sp_rename ' + QUOTENAME(TABLE_SCHEMA + '.'+TABLE_NAME,'''') + ',' + TABLE_NAME + '_Deleted;
'
FROM information_schema.tables
WHERE table_schema IN ('NJ', 'AZ', 'MN')
AND RIGHT(TABLE_NAME,8) <> '_Deleted'
;
PRINT @SQL
;
--Jeff Moden
Change is inevitable... Change for the better is not.
August 23, 2018 at 10:10 pm
Jeff Moden - Thursday, August 23, 2018 10:09 PMIf you're going to use sp_rename to rename tables other than dbo (or whatever your default schema is), you must specify the schema name in the first operand of sp_rename along with the table name as a 2 part named object'. You also need single quotes when doing so. Last but not least, you need to protect the renamed tables from being renamed again if another execution of the code occurs.
DECLARE @SQL NVARCHAR(max)=''
;
SELECT @SQL += 'exec sp_rename ' + QUOTENAME(TABLE_SCHEMA + '.'+TABLE_NAME,'''') + ',' + TABLE_NAME + '_Deleted;
'
FROM information_schema.tables
WHERE table_schema IN ('NJ', 'AZ', 'MN')
AND RIGHT(TABLE_NAME,8) <> '_Deleted'
;
PRINT @SQL
;
p.s. If you have some wonky table names, you may have to do additional encapsulation of names in brackets using more QUOTENAME.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 24, 2018 at 8:15 am
Jeff Moden - Thursday, August 23, 2018 10:10 PMJeff Moden - Thursday, August 23, 2018 10:09 PMIf you're going to use sp_rename to rename tables other than dbo (or whatever your default schema is), you must specify the schema name in the first operand of sp_rename along with the table name as a 2 part named object'. You also need single quotes when doing so. Last but not least, you need to protect the renamed tables from being renamed again if another execution of the code occurs.
DECLARE @SQL NVARCHAR(max)=''
;
SELECT @SQL += 'exec sp_rename ' + QUOTENAME(TABLE_SCHEMA + '.'+TABLE_NAME,'''') + ',' + TABLE_NAME + '_Deleted;
'
FROM information_schema.tables
WHERE table_schema IN ('NJ', 'AZ', 'MN')
AND RIGHT(TABLE_NAME,8) <> '_Deleted'
;
PRINT @SQL
;p.s. If you have some wonky table names, you may have to do additional encapsulation of names in brackets using more QUOTENAME.
Perfect, I just replaced 'print' with EXEC sp_executeSQL and it worked perfectly. Thank you as always Jeff!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply