Posted by Uri Dimant on 01/22/08 08:35

Copy -Paste from Tony articles

In SQL 2005 there is, sort of. This is query lists the last execution
time for all SQL modules in a database:

SELECT object_name(m.object_id), MAX(qs.last_execution_time)
FROM sys.sql_modules m
LEFT JOIN (sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st)
ON m.object_id = st.objectid
AND st.dbid = db_id()
GROUP BY object_name(m.object_id)

But there are tons of caveats. The starting point of this query is
the dynamic management view dm_exec_query_stats, and the contents is
per *query plan*. If a stored procedure contains several queries,
there are more than one entry for the procedure in dm_exec_query_stats.



"kellygreer1" <kellygreer1@yahoo.com> wrote in message
news:8fa4846e-0434-4bbe-9bfd-c9c673183d34@v46g2000hsv.googlegroups.com...
> Sometimes at the end of a project you'll end up with unused Tables,
> Stored Procs, Functions, and Views.
> Since there is not something like a 'SELECT' trigger ... what is the
> best way for telling what is not being used by your solution? To turn
> on some kind of tracing?
>
> Any ideas?
>
> Thanks,
> Kelly Greer
> kellygreer1@nospam.com
> change nospam to yahoo
>

[Back to original message]