Solved

How do you batch Rename Tables in SQL Server

Posted on 2004-09-02
9
761 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql server insert 13 31
Sql server, import complete table, using vb.net 9 35
Sql server get data from a usp to use in a usp 5 16
Merge two rows in SQL 4 14
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

809 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