We help IT Professionals succeed at work.

Querying SYSObjects to rename SP code

stankstank
stankstank asked
on
Medium Priority
433 Views
Last Modified: 2008-02-01
I have over 200 stored procedures in my database and about 90 tables.  I have renamed some of the schemas and tables, so I have to change code in about 90% or more of the stored procedures.  I have done about 20 so far, and I am beat... is it possible to query sysobjects for code, or table references inside of the stored procedures?

for example, if I have a stored procedure with this code (select * from [Inventory].tbl_Inventory) and I change it to (select * from [Inventory].Products) how can I automate this?  I want to search all stored procedures with have the tbl_Inventory and replace it with tbl_Products.  Is this possible?

Thank you,

StankStank
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2011
Commented:
extract all your stored procedure to a single file (use the gererate sql option  from right click on data base ... all tasks...)


then in Query analyser use search and replace (from edit) to convert your code...

then reapply the stored procedures

and test test test....

hope you're doing this in a test / development database...

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Commented:
Quickly identify consuming objects (will return views, functions, stored procs, etc)
SELECT
  so.name,
  so.id,
  so.xtype
FROM
  sysobjects so
INNER JOIN syscomments sc ON  sc.id=so.id
AND sc.text LIKE '%tbl_Inventory%'

Won't automatically apply. And like Lowfatspread said - test!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.