Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

3 Answers 3

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
share|improve this answer

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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.