renamed table [need to quickly update 200 SPs]

Hi
I have had to rename one of my tables (sp_Rename []) I now need to update
all my stored procedures, I want to some how use syscomments (maybe) and
loop through my SPs using REPLACE command to change the references to the
old table name
Any help / advise appreciated
LukeHi,
One solution is, you can generate a script with all the SPs available.
Open some text editor and use Replace All option giving your old and new
File Name.
Run the script again in Query Analyser
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Luke Ward" wrote:

> Hi
> I have had to rename one of my tables (sp_Rename []) I now need to update
> all my stored procedures, I want to some how use syscomments (maybe) and
> loop through my SPs using REPLACE command to change the references to the
> old table name
> Any help / advise appreciated
> Luke
>
>|||Don't fiddle with system tables ever! I would hope you have some
sort of code mgmt (ie VSS) in place where your master copies of
all procs are kept. That's where your changes should be made, tested
against a copy of your production data, and then applied to the
production database when you're satisfied with the results.
I think your best bet is to manually change the table name within
the procs. If you know a scripting language (like Perl), you could
write a script to blow through the procs and make the change and then
compile them back into the database.. That's what I would do.
"Luke Ward" <lukeward@.campbelluk.com> wrote in message
news:e6GC%23xsWFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi
> I have had to rename one of my tables (sp_Rename []) I now need to update
> all my stored procedures, I want to some how use syscomments (maybe) and
> loop through my SPs using REPLACE command to change the references to the
> old table name
> Any help / advise appreciated
> Luke
>

Rename of stored proc in EM/Manage Studio does not work as expecte

I normally rename stored procedures through script, so I never noticed this
before, but if you use EM (2000) or Server Management Studio (2005) to renam
e
a stored procedure the name changes in the GUI, but the actually name used t
o
execute it does not. So, if I start with a stored procedure named 'a' it
executes using EXEC a. Now, I rename it through the GUI to b and EXEC a
still works and EXEC b fails. If you open the stored procedure for editing
you can see that in the code the stored procedure name has not been changed.
Whereas if you rename a view of function the name within the code is actuall
y
changed.
Can anyone explain this behavior?> So, if I start with a stored procedure named 'a' it
> executes using EXEC a. Now, I rename it through the GUI to b and EXEC a
> still works and EXEC b fails.
This is not what I am seeing. I created a proc named aa in tempdb. I renamed
it to a using EM. EM
submitted the following SQL:
EXEC sp_rename N'[dbo].[aa]', N'a', N'object'
I can now execute a. If I try to execute aa, I get "could not find stored pr
ocedure".
My guess is that you have procs with same name but different owners. Check s
ysobjects.
Also, be aware that sp_rename does not change the source code for the proced
ure (see syscomments),
the original name will be in the source code.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Byron" <Byron@.discussions.microsoft.com> wrote in message
news:3102CE8C-BA55-49C2-AB12-65BF91CDF87E@.microsoft.com...
>I normally rename stored procedures through script, so I never noticed this
> before, but if you use EM (2000) or Server Management Studio (2005) to ren
ame
> a stored procedure the name changes in the GUI, but the actually name used
to
> execute it does not. So, if I start with a stored procedure named 'a' it
> executes using EXEC a. Now, I rename it through the GUI to b and EXEC a
> still works and EXEC b fails. If you open the stored procedure for editin
g
> you can see that in the code the stored procedure name has not been change
d.
> Whereas if you rename a view of function the name within the code is actua
lly
> changed.
> Can anyone explain this behavior?sql

Rename of stored proc in EM/Manage Studio does not work as expecte

I normally rename stored procedures through script, so I never noticed this
before, but if you use EM (2000) or Server Management Studio (2005) to rename
a stored procedure the name changes in the GUI, but the actually name used to
execute it does not. So, if I start with a stored procedure named 'a' it
executes using EXEC a. Now, I rename it through the GUI to b and EXEC a
still works and EXEC b fails. If you open the stored procedure for editing
you can see that in the code the stored procedure name has not been changed.
Whereas if you rename a view of function the name within the code is actually
changed.
Can anyone explain this behavior?> So, if I start with a stored procedure named 'a' it
> executes using EXEC a. Now, I rename it through the GUI to b and EXEC a
> still works and EXEC b fails.
This is not what I am seeing. I created a proc named aa in tempdb. I renamed it to a using EM. EM
submitted the following SQL:
EXEC sp_rename N'[dbo].[aa]', N'a', N'object'
I can now execute a. If I try to execute aa, I get "could not find stored procedure".
My guess is that you have procs with same name but different owners. Check sysobjects.
Also, be aware that sp_rename does not change the source code for the procedure (see syscomments),
the original name will be in the source code.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Byron" <Byron@.discussions.microsoft.com> wrote in message
news:3102CE8C-BA55-49C2-AB12-65BF91CDF87E@.microsoft.com...
>I normally rename stored procedures through script, so I never noticed this
> before, but if you use EM (2000) or Server Management Studio (2005) to rename
> a stored procedure the name changes in the GUI, but the actually name used to
> execute it does not. So, if I start with a stored procedure named 'a' it
> executes using EXEC a. Now, I rename it through the GUI to b and EXEC a
> still works and EXEC b fails. If you open the stored procedure for editing
> you can see that in the code the stored procedure name has not been changed.
> Whereas if you rename a view of function the name within the code is actually
> changed.
> Can anyone explain this behavior?

Rename a linked server

I have an app that has some stored procedures running queries across a linked server. In our development environment, the linked database is on a box called 10.xxx.xxx.xx. In the production environment, the linked database will be on a server called something else. What I want to do is have the linked server in the development environment named the same thing as the linked server in the production environment, so that I won't have to change the stored procedures when migrating the project into production.

SQL Server 2005 isn't giving me the option to rename or alias the linked server, though. Is there anyway to do this so that I can have the linked server named the same thing on the two different boxes and not have to change the sp's?

I don't think you can rename linked servers, but you can create them correctly for your needs. Don't select SQL Server, but do select the SQL native client, or just use T-SQL which may make more sense if trying to standardise stuff accross machines anyway

EXEC sp_addlinkedserver @.server= 'FRED1' ,@.provider='SQLNCLI' ,@.datasrc='10.1.1.1' ,@.srvproduct = 'SQL'

In future please try and pick a more appropriate forum. This is really SQL Server question issue, not SSIS.

sql