• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 811
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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