report when was last used the stored procedure/view- in a DB

hello,
is there a possibilitie to get the date when a stored procedure / view was
used
The problem is that in a database there are a lot of views - which where
created in a period of last 2 years from diffren user which had access to th
e
database. Now i want to have a kind of report for all views / stored
procedures from a database - to see if this where used in the last 3 month.
If no we can than make a backap and delete this.
best regardsYou could do the stored procedures by either using Profiler and auditing
whats executed against the SQL Server and then pull out the procs used, or
you could write into each stored procedure a write to an audit table
something like...
insert sp_run_log ( procname ) values ( 'myprocname' )
Then you can check through the log after a period of time - you would need
to run every piece of logic in yoru application though.
I can't think of anyway you could do a view, you could perhaps trap the SQL
again using profiler at the statement level and do string searches for them.
Hope that helps.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:07E773A8-9A95-4CB5-B839-72AA796BF2CE@.microsoft.com...
> hello,
> is there a possibilitie to get the date when a stored procedure / view
> was
> used
> The problem is that in a database there are a lot of views - which where
> created in a period of last 2 years from diffren user which had access to
> the
> database. Now i want to have a kind of report for all views / stored
> procedures from a database - to see if this where used in the last 3
> month.
> If no we can than make a backap and delete this.
> best regards|||hello Tony,
thanks for yur help, the idea to write in a audit table is great.
For the view ..... have you any link / information / step by step .. how
to realize the " trap the SQL using profiler at the statement level ..."
thanks
"Tony Rogerson" wrote:

> You could do the stored procedures by either using Profiler and auditing
> whats executed against the SQL Server and then pull out the procs used, or
> you could write into each stored procedure a write to an audit table
> something like...
> insert sp_run_log ( procname ) values ( 'myprocname' )
> Then you can check through the log after a period of time - you would need
> to run every piece of logic in yoru application though.
> I can't think of anyway you could do a view, you could perhaps trap the SQ
L
> again using profiler at the statement level and do string searches for the
m.
> Hope that helps.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:07E773A8-9A95-4CB5-B839-72AA796BF2CE@.microsoft.com...
>
>