[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 421
  • Last Modified:

Querying SYSObjects to rename SP code

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
0
stankstank
Asked:
stankstank
2 Solutions
 
LowfatspreadCommented:
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...
0
 
NightmanCTOCommented:
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!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now