Solved

EXECUTE MULTIPLE SP_RENAME STATEMENTS WITHIN QUERY ANALYZER

Posted on 2004-04-23
3
1,347 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
Comment Utility
put GO in between each statement to start the next batch....
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
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 …
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

743 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

7 Experts available now in Live!

Get 1:1 Help Now