Is there a way to view Stored Procedure, Trigger andFunction Usage in SQL Server?

[marcsirois@gmail.com]

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

[abc]

Re: Is there a way to view Stored Procedure, Trigger andFunctionUsag e in SQL Server?

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@gmai l.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
>

[marcsirois]

Re: Is there a way to view Stored Procedure, Trigger andFunction Usage in SQL Server?

Thanks Adrian,

Thanks for the reply. This is indeed helpful.


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@gmai l.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

[Ed Murphy]

Re: Is there a way to view Stored Procedure, Trigger andFunctionUsag e in SQL Server?

abc wrote:

SELECT *
FROM syscomments
WHERE TEXT LIKE '%<your function name>%'

Doesn't this miss instances where <your function nameis split
across two chunks of text?

[marcsirois]

Re: Is there a way to view Stored Procedure, Trigger andFunction Usage in SQL Server?

You're right, Ed. For instance, if <function nameis 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


Ed Murphy wrote:

abc wrote:
>

SELECT *
FROM syscomments
WHERE TEXT LIKE '%<your function name>%'

>
Doesn't this miss instances where <your function nameis split
across two chunks of text?

[xAvailx]

Re: Is there a way to view Stored Procedure, Trigger andFunction Usage in SQL Server?

> 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


marcsirois@gmai l.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