sql server (alternate):
I am maintaining an application where most of the business rules are in
Triggers, Stored Procedures and User Defined Functions. When a bug
arises, it can get very tedious to debug. Today for example, I wanted
to modify a function that was being called by a trigger. The problem
is that I don't want to change the function, for fear that it is being
called by one of the other SP's or triggers in the database (there are
hundreds of them)
Essentially, I need a tool that allows me to view where functions and
sp's are being referenced from. At the very least, I'd like to perform
a "full text search" in the database objects, so that let's say I have
a function named "fn_doSomething", I can search the schema for this
string and get all the places where it appears.
As you can see, I'm in the dark here. I've never worked on a system
where all business rules are at the database level. If you know of a
tool that does what I describe above, or anything else that would
facilitate my life, please let me know!
Thanks for your help,
Marc
Not sure about 2005 but in 2000 use syscomments SELECT * FROM syscomments WHERE TEXT LIKE '%<your function name>%' Now add sysobjects to get the name of the stored procedure where your function is called. If you are using QA instead of that awful thing in 2005, create a stored procedure in the master database add a shortcut key sequence in QA, highlight the function name, press the shortcut and all instances will magically appear. Adrian [quoted text, click to view] marcsirois@gmail.com wrote:
> I am maintaining an application where most of the business rules are in
> Triggers, Stored Procedures and User Defined Functions. When a bug
> arises, it can get very tedious to debug. Today for example, I wanted
> to modify a function that was being called by a trigger. The problem
> is that I don't want to change the function, for fear that it is being
> called by one of the other SP's or triggers in the database (there are
> hundreds of them)
>
> Essentially, I need a tool that allows me to view where functions and
> sp's are being referenced from. At the very least, I'd like to perform
> a "full text search" in the database objects, so that let's say I have
> a function named "fn_doSomething", I can search the schema for this
> string and get all the places where it appears.
>
> As you can see, I'm in the dark here. I've never worked on a system
> where all business rules are at the database level. If you know of a
> tool that does what I describe above, or anything else that would
> facilitate my life, please let me know!
>
> Thanks for your help,
>
> Marc
Thanks Adrian, Thanks for the reply. This is indeed helpful. [quoted text, click to view] abc wrote:
> Not sure about 2005 but in 2000 use syscomments
>
> SELECT *
> FROM syscomments
> WHERE TEXT LIKE '%<your function name>%'
>
> Now add sysobjects to get the name of the stored procedure where your
> function is called. If you are using QA instead of that awful thing in
> 2005, create a stored procedure in the master database add a shortcut
> key sequence in QA, highlight the function name, press the shortcut and
> all instances will magically appear.
>
> Adrian
>
>
>
> marcsirois@gmail.com wrote:
> > I am maintaining an application where most of the business rules are in
> > Triggers, Stored Procedures and User Defined Functions. When a bug
> > arises, it can get very tedious to debug. Today for example, I wanted
> > to modify a function that was being called by a trigger. The problem
> > is that I don't want to change the function, for fear that it is being
> > called by one of the other SP's or triggers in the database (there are
> > hundreds of them)
> >
> > Essentially, I need a tool that allows me to view where functions and
> > sp's are being referenced from. At the very least, I'd like to perform
> > a "full text search" in the database objects, so that let's say I have
> > a function named "fn_doSomething", I can search the schema for this
> > string and get all the places where it appears.
> >
> > As you can see, I'm in the dark here. I've never worked on a system
> > where all business rules are at the database level. If you know of a
> > tool that does what I describe above, or anything else that would
> > facilitate my life, please let me know!
> >
> > Thanks for your help,
> >
> > Marc
> >
[quoted text, click to view] abc wrote:
> SELECT *
> FROM syscomments
> WHERE TEXT LIKE '%<your function name>%'
Doesn't this miss instances where <your function name> is split
You're right, Ed. For instance, if <function name> is referenced from 8 different places in a stored procedure, it will only return 1 result. That's why I was asking about some sort of 3rd party tool that would allow you to seasrch for a string, return all the instances where it's found, and let you navigate across the results. Ideally, you'd click on a stored procedue for example, and the tool would return all areas where this stored procedure is called, and vice versa. I'm sure something like this exists, it's just a matter of finding it. Unfortunately, my google skill are failing me. But for now, the "select from syscomments" solution will do. Thanks, Marc [quoted text, click to view] Ed Murphy wrote:
> abc wrote:
>
> > SELECT *
> > FROM syscomments
> > WHERE TEXT LIKE '%<your function name>%'
>
> Doesn't this miss instances where <your function name> is split
> across two chunks of text?
[quoted text, click to view] >> The problem is that I don't want to change the function, for fear that it is being called by one of the other SP's or triggers in the database (there are hundreds of them) <<
It is considered good practice to have your database objects scripted and in source code control. If that is the case, then you can use any text editor to search and manipulate the sps, triggers, udfs, etc... If that is not the case (I would still recommend scripting), I found this post with a util stored proc that you may find useful (don't know if it works or not...) http://groups.google.com/group/comp.databases.ms-sqlserver/browse_thread/thread/8a080b51fdc550b2/b83473d28f272d4%23b83473d28f272d4 HTH [quoted text, click to view] marcsirois@gmail.com wrote:
> I am maintaining an application where most of the business rules are in
> Triggers, Stored Procedures and User Defined Functions. When a bug
> arises, it can get very tedious to debug. Today for example, I wanted
> to modify a function that was being called by a trigger. The problem
> is that I don't want to change the function, for fear that it is being
> called by one of the other SP's or triggers in the database (there are
> hundreds of them)
>
> Essentially, I need a tool that allows me to view where functions and
> sp's are being referenced from. At the very least, I'd like to perform
> a "full text search" in the database objects, so that let's say I have
> a function named "fn_doSomething", I can search the schema for this
> string and get all the places where it appears.
>
> As you can see, I'm in the dark here. I've never worked on a system
> where all business rules are at the database level. If you know of a
> tool that does what I describe above, or anything else that would
> facilitate my life, please let me know!
>
> Thanks for your help,
>
> Marc
Don't see what you're looking for? Try a search.
|
