Post reply

Updating all stored procedures within a database

  • April 15, 2008 at 7:02 am

    #185330

    I have a group of databases, one per customer and another database common to all customers

    I've been doing all my development work on one of the customer's database (a test copy, please!)

    I wrote a function on that database - the lead programmer asked me to copy that function to the common database, which I did, and to refer to the copy

    Is there a way to find all the places (i.e. stored procedures) I used the function in?

    I could create a massive file of all the stored procedures and do a Find but is there a better way?

  • April 15, 2008 at 7:30 am

    #802558

    Search this site a little. There are lots of postings for how to search procedure text. It is not hard to search the syscomments view, but there are some tricky points so it would be good to look for something that has been well tested.

  • April 15, 2008 at 11:16 am

    #802715

    select name, definition

    from sys.sql_modules modules

    inner join sys.all_objects objects

    on modules.object_id = objects.object_id

    where definition like '%' + YourObjectName + '%'

    Replace "YourObjectName" with the name of your function, or with a variable, and use that. This also works for finding which procs use a column name, table name, etc.

    Syscomments is obsolete in SQL 2005 (which is what I assume you're using because of the forum this is posted in). It's still there, for backwards compatibility, but sys.sql_modules is recommended.

    I have found that some databases upgraded from SQL 2000 to SQL 2005 have different names for the proc in the Name column and in the create script in the Definition column. This seems to be a problem caused by the "rename" function in SQL 2000. So, don't just select the definitions and use them to create a set of "alter" scripts.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • April 16, 2008 at 6:44 am

    #803046

    thanks!

    I knew I could come up with a method that would get the results I needed but I didn't know the right way to do it

    🙂

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply