Finding Stored procedures being used

Question

Finding Stored procedures being used

Jonathan Stokes

SSCrazy Eights

Points: 9861
More actions
January 18, 2005 at 3:15 am

#60595

I am hoping someone can help me out with a currnet problem. My objective is to identify all the stored procedures that are used in our datawarehouse during our load process. I have managed to retrieve them by looking at jobs called along the way etc. I now want to check for stored procedures call by other stored procedures. I can do this manually by going into EM and selecting <all tasks> <Display dependencies>. I want to be able to script out all the stored procedures and their dependencies into a table. I can then use this to drop all stored procedures no longer required.

In the long term, I want to be able to keep a track of all the stored procedures used during the load process to make sure they are all still being utilised. I know that I can write an insert into a dummy table at the end of each sp to check that it was run, but as there are over 500 of them and still growing, it will take some maintenance. What I really want is a sort of trigger to fire every time a stored procedure is called which then dynamically populates a dummy table.
In case you are wondering, we do not have a change control system, but the process I am undertaking will then be used to create one.
In summary, I just need a short term answer to tidy up what we have and some ideas on what to do in the long term.
------------------------------
The Users are always right - when I'm not wrong!

Viewing 10 posts - 1 through 9 (of 9 total)

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Well, I'll try to come up with some good advice then

Firstly - forget 'display dependiencies' - it doesn't work like we want it to work, and it's not 100% reliable. So, if you want to do this stuff once, and do it right, you can't use that 'functionality'.

Imho, the only way, though it's also the boring and tedious way, but - still - the only way to get it right - is to review code. Profiler might be of some help as well.

Yup, get in there - pen and paper, whiteboard, notepad, modelling software, whatever you fancy, and start mapping the whole shebang. When you're done, you have documentation! Keep it in a couple of safe places, but if you by then don't have any kind of change control system/methods/whatever implemented, chances are you'll have to do it all over again in the not so far future..

To avoid that, protect the documented map when it's final at all costs. When something changes, change the map/docs as well!

Imo there is no short term 'workaround' that's useful in any way, if it's going to be worth the effort, it must be right, and that's the long term solution. Above all, in both the short and the long term, you must have change control in effect, else it's all OK Corall again.

/Kenneth

David Burrows SSC Guru Points: 65138 More actions · Answer 2

Well Kenneth says it all

Looking at the code is the only surest way. What I would do is script all the procs into separate files and use DOS FINDSTR looking for EXEC and then process the output.

If you do want to use the dependancies then

exec sp_MSdependencies N'?'

will give you details of the parameters to use to produce a script to list the results (even into a table if you wish).

Far away is close at hand in the images of elsewhere.
Anon.

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 3

Thank you both for your replies. Somehow I knew it would involve a lot of work, but if it is to be right, then I appreciate it's the only way.

Looks like I've got my work cut out for this week.

------------------------------
The Users are always right - when I'm not wrong!

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

Don't forget the thing about change control. It's better to work on that as well, so it is ready when you're done with the mapping. If not, it may be too tempting to go around it while waiting for it to become real, and before you know it, you might have to do the work all over again.

/Kenneth

David Burrows SSC Guru Points: 65138 More actions · Answer 5

Hmm, seems like you've been there and done that Kenneth

Far away is close at hand in the images of elsewhere.
Anon.

Peter Wright SSCommitted Points: 1966 More actions · Answer 6

The Profiler will be a great help. You will have to set some restrictions on the Events and Filters tabs or you will be deluged with data. Experiment and save your best effort as a Trace Template.

For example:

Try removing all Events apart from SQL:BatchCompleted. This will list stored procedures & SQL Statements.

Under filters try setting NTUserName or LoginName to the name of the user running the process you want to trace.

David Burrows SSC Guru Points: 65138 More actions · Answer 7

Peter,

That is fine as long as the procedures are called in sequence, if there is any logic that may or may not execute one or more procedures (or series of procedures) then unless every possible combination is tried profiler will not show some procedures.

But it is another tool, in a set of many, that may help.

Far away is close at hand in the images of elsewhere.
Anon.

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 8

Thanks Peter. The user account that is logged on to perform the load is not a global one, so I will be able to put a trace on it.

I will give it a go and see if matches with the partial data I already have. I totally diidn't think of doing it that way. Thanks for pointing it out.

------------------------------
The Users are always right - when I'm not wrong!

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 9

I've been around for a while... You tend to see some 'stuff' from time to time...

/Kenneth