SQL Stored Proc's

[AMDRIT]

couldn't find a group just for SQL Server. Anyone know if it is possible
to retrieve the name of the stored procedure that is currently executing?
What I would like to do is record usage in a table everytime a stored proc
is executed.



create procedure blahblah

as

--Update Usage Log
-- optionally test to see if monitoring is on (custom run time variable?)
-- figure out how to get storedproc's name
insert into usagelog (OwnerName, StoredProcName, UsageDate) values
(CurrentProcedureOwner, CurrentProcedureName, GetDate())

--Complete stored procedure
Select * from myblahblahtable

 

[William \(Bill\) Vaughn]

did you try microsoft.public.sqlserver.programming?
In any case you need to enable the Profiler. It can record all or filtered
activity on any specified server.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[AMDRIT]

Thanks, I had MS Outlook Express looking for the sql server news groups, and
wansn't able find them connected to news.microsoft.com, I did find it under
msnews.microsoft.com.

Anyway, Profiler isn't what I want to do. I want the stored procs to
document their usages over time. We have a number of databases and
development teams, we do not have a dedicated DBA or DBA group. Over time,
people float in and out of the teams and as a result some stored procedures
become orpahned.

The prodceedures themselves are documented pretty well, there is just a
large volume of them. So periodically I want to have them record when they
are used, so that we may narrow the list of proceedures to weed out.
Profiler will do the same, however, the number of filters you would have to
put in place just to get the results you are looking for is unmanageable.

I just went through all the proceedures and manually typed the proceedure
name and owner in the insert statement. I was trying to be lazy and make
use of a function that would provide me with that information so I can
merely copy and paste without editing the statement.

 

[Guest]

You can use @@PROCID (which is available within any stored procedure)
and then query the name column on dbo.SYSOBJECTS

SELECT NAME FROM SYSOBJECTS WHERE ID = @@PROCID

 

[AMDRIT]

Excellent, thanks. Do you know if @@ProcID remains constant over all
versions of the stored proceedure, and is only replaced when the object is
dropped and recreated, perhaps I can skip an extra read and just store the
procid?

 

[William \(Bill\) Vaughn]

the ID is set when the procedure is created and changes when it's recreated
but not changed when it's altered.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------