Solved

How do you batch Rename Tables in SQL Server

Posted on 2004-09-02
9
749 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
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 26

Assisted Solution

by:Hilaire
Hilaire earned 125 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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:ScottPletcher
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 42

Accepted Solution

by:
EugeneZ earned 125 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

930 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now