Object Access History

Question

Object Access History

BC-394365

SSC Enthusiast

Points: 148
More actions
March 1, 2007 at 11:34 am

#68890

I'm looking for a way to monitor the usage of SQL views and stored procedures. I have several hundred views across quite a few servers with no documentation...
Some views are redundant for sure but I would like to find out which views are being used, if possible.

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

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

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

a profiler trace would be your only real option, unless you write an audit table update into each object , you can use something like this in a proc

insert into mytable(col1,col2,col3)
select object_name(@@procid),user_name(),getdate()

I don't have any ideas on views other than a profiler trace that only picks views, triggers don't work on selects - hopefully someone else will have an idea on views.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Nothing to add here. Profiler is the only real option.

You can add the code to all the sps, but that still leave a lot of holes in the code (direct table/view/function access).

BC-394365 SSC Enthusiast Points: 148 More actions · Answer 3

I have been playing with the profiler and it should give me what I need if I let it run a while. What I have not found yet is which filter will show the name of the object being accessed, view or stored procedure. Does anyone know which filter will return that information?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

I can't seem to get this column to show any data. Right now all I can offer is to parse the command text for object names... For from the best solution if you want my opinion.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 5

I've never found the names show, capture the object id and then join back to get the name, or use object_name(id) if only one database

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

BC-394365 SSC Enthusiast Points: 148 More actions · Answer 6

I figured I might have to parse the Object ID from the output and query the System Tables to retrieve the objects name. Just another step...

Thanks for the support.

ShekharNaidu SSCrazy Points: 2092 More actions · Answer 7

here is my situation wid the SQL profiler

when I save the trace in to a database table, the query text is been trimed to 256 characters.. is there any way to store the complete query text in table without any trim????...

Thanks ,

Shekhar

Maverick100877 SSCertifiable Points: 5240 More actions · Answer 8

Shekar,

the data would have got saved.Its just that the maximum charaters that you can view in result is set to 356. increase it to 8000 and then probably you will be able to see the results.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 9

and doesn't this catch so many people out !!

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Ninja's_RGR'us

SSC Guru

Points: 294069

March 9, 2007 at 8:06 am

#694107

Stopped counting many moons ago.