Solved

EXECUTE MULTIPLE SP_RENAME STATEMENTS WITHIN QUERY ANALYZER

Posted on 2004-04-23
3
1,398 Views
Last Modified: 2008-03-03
Howdy,

Can anyone tell me how to execute multiple sp_rename statements from within query analyzer. I have quite a few tables that need to be renamed from tableone to tableone_old  and a corresponding number that need to be renamed from tableone_new to tableone as in the following example:

sp_rename ZIPS,ZIPS_OLD                        
sp_rename ZIPS1,ZIPS1_OLD      
sp_rename ZIPS_NEW,ZIPS                  
sp_rename ZIPS1_NEW,ZIPS1      

executing this generates a result of:

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'sp_rename'.

Thanks,

Del
0
Comment
Question by:delclifton
  • 2
3 Comments
 
LVL 34

Accepted Solution

by:
arbert earned 500 total points
ID: 10905888
put GO in between each statement to start the next batch....
0
 
LVL 34

Expert Comment

by:arbert
ID: 10905894
If you want to rename every table in a database, you can also do this:

sp_msforeachtable 'sp_rename ''?'',''?_old'''


Notice the above are NOT double quotes--they are double single quotes.

Brett
0
 

Author Comment

by:delclifton
ID: 10905941
Dang! Do I feel stupid. I had tried the GO but it was on each line i.e.

go sp_rename ZIPS,ZIPS_OLD                    
go sp_rename ZIPS1,ZIPS1_OLD

but got other errors. Putting go between each line did the trick. Thanks!

Del
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

828 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