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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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;
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


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

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

  PRINT @sql;

Or by specifying the database in the EXEC:


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

As stated in

share|improve this answer
MyDB2..sp_rename is simplest by far! – Michel de Ruiter Mar 16 at 11:37

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.

share|improve this answer

Your Answer


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