?
Solved

How do you batch Rename Tables in SQL Server

Posted on 2004-09-02
9
Medium Priority
?
799 Views
Last Modified: 2008-01-09
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
Comment
Question by:DSchat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 26

Assisted Solution

by:Hilaire
Hilaire earned 500 total points
ID: 11962755
try

exec sp_rename 'old_name', 'new_name'
0
 

Author Comment

by:DSchat
ID: 11963137
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
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11963215
EXEC sp_rename 'Region', Area'
You are missing a single quote before AREA

do this

EXEC sp_rename 'Region', 'Area'
JAY
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 26

Expert Comment

by:Hilaire
ID: 11963218
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
 

Author Comment

by:DSchat
ID: 11963374
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
 
LVL 34

Expert Comment

by:arbert
ID: 11963625
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
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11964472
oooh that would be bad
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 11964896
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
 
LVL 43

Accepted Solution

by:
Eugene Z earned 500 total points
ID: 11965449
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

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question