Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 825
  • Last Modified:

How do you batch Rename Tables in SQL Server

I would like to fill several tables in a db with a dts job and then at the sucessful end of the job drop my original tables and rename the new ones to match the names of the just dropped tables. Is that possible in SQL Server?
0
DSchat
Asked:
DSchat
  • 2
  • 2
  • 2
  • +3
2 Solutions
 
HilaireCommented:
try

exec sp_rename 'old_name', 'new_name'
0
 
DSchatAuthor Commented:
I just tried that on my server on Northwind and received the following:

EXEC sp_rename 'Region', Area'

Server: Msg 15225, Level 11, State 1, Procedure sp_rename, Line 273
No item by the name of 'Region' could be found in the current database 'Northwind', given that @itemtype was input as '(null)'.

when I added 'OBJECT' to the query i received the this message:

EXEC sp_rename 'Region', Area', 'OBJECT'

Server: Msg 15248, Level 11, State 1, Procedure sp_rename, Line 223
Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong.

0
 
Jay ToopsCommented:
EXEC sp_rename 'Region', Area'
You are missing a single quote before AREA

do this

EXEC sp_rename 'Region', 'Area'
JAY
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
HilaireCommented:
I don't have a northwind db under my hand (production site), but it works for me.

You don't need to specify @objtype for tables
Also there's missing quotes in the code you posted above

0
 
DSchatAuthor Commented:
The missing quote was only here where I described my query. In Query analyzer it was there, I would've gotten a Syntax error message, had I forgotten it there.

Anyways...... I tried it again on my developement db, where I will actually be needing it and there it worked!

0
 
arbertCommented:
Agree--drop the @object type and let sql do that on its own...Also, make sure you don't have multiple objects  in Northwind called REgion....
0
 
Jay ToopsCommented:
oooh that would be bad
0
 
Scott PletcherSenior DBACommented:
Personally, I would specify the type, to avoid SQL getting "confused" if two things did happen to have the same name.  So:

EXEC sp_rename 'old_name', 'new_name', 'OBJECT'


Also, remember that if the owner is not dbo, I think you need to specify it on the first parameter only (you cannot rename the table and change the owner at the same time):

EXEC sp_rename 'user1.old_name', 'new_name', 'OBJECT'

0
 
Eugene ZCommented:
Based on BOL  due to rename table do not need use 'OBJECT'
just
EXEC sp_rename 'old_name', 'new_name',
//see above comments - about owners of objects - dbo or who/
 
Try sample from http://www.databasejournal.com/features/mssql/article.php/1458151




1) How to Rename a Table

Renaming table 'Sales' to 'Orders'.

IF Exists (Select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
Exec sp_rename 'Sales', 'Orders'
 
IF @@Error <> 0
Raiserror('Failed to rename Table Sales to Orders',16,1)
ELSE
Print 'Table Sales Renamed to Orders'
END
ELSE
Print 'Table Sales does not exist'
GO
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 2
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now