10

Is there a way in SQL Server to rename a table in another database? When you are in current database and migrating data over to another database in the TEMP table and then renaming TEMP table in that another database. I do not want to use "USE [database]" since the database name could change on different servers.

Thanks for the help in advance!

4 Answers4

7

You should use dynamic sql to do this. Build a string and execute it. As long as you know the name of the destination database, everything should be fine.

use CurrentDB
declare @x varchar(1000), @otherDb sysname = 'NewDB';

set @x = 'use ' + @otherDB + '

EXEC sp_rename ''dbo.temp'', ''temp2''
'
exec (@x);
print @x;
GO
Marian
  • 15,531
  • 2
  • 60
  • 74
4

Using dynamic SQL

  USE MyDB1
  GO

  DECLARE @db_name   NVARCHAR(20)   = 'myDB2'
  DECLARE @sql       NVARCHAR(1000)

  SET @sql = N'EXEC '+ quotename(@db_name)+ '..sp_rename ''[MyTable]'', ''[YourTable]'';';

  PRINT @sql;
  EXEC SP_EXECUTESQL @sql;

Or by specifying the database in the EXEC:

USE MyDB1
GO

EXEC MyDB2..sp_rename 'MyTable', 'YourTable'

As stated in http://www.sqlservercentral.com/Forums/Topic931229-1292-1.aspx

Aaron Bertrand
  • 180,303
  • 28
  • 400
  • 614
yucer
  • 140
  • 3
3

It's certainly possible, but do remeber that dynamic SQL should be avoided when possible. It easily leads to security and performance problems.

After thinking about your question a bit are you sure it wouldn't make more sense to create the other table with the correct name and then simply INSERT into it from the TEMP table? I'm assuming the TEMP table is actually a local/global temp table or table variable.

AndrewSQL
  • 1,088
  • 5
  • 13
0

You can also use SSMS. The advantage of using SSMS is that it will go through your references and constraints and rename those as well. Simply open the object browser in SSMS find the table of interest and rename it. If you have foreign key constraints in other tables that refer to the table being renamed it will rename the table to the new table name in those key definitions as well.

John McPherson
  • 1