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 (AT) nospam (DOT) com
change nospam to yahoo

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 (AT) yahoo (DOT) com> wrote

Uri Dimant (urid (AT) iscar (DOT) co.il) writes:
And very importantly: it only lists what's in the cache. A procedure could
fall out of the cahce for several reasons. For instance, adding an index
on a table, flushes all plans related to that table. And a server restart
empites the cache entirely.

I would be very careful to use information of that kind to draw
conclusion of whether an object is in use.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Jan 21, 6:36 pm, kellygreer1 <kellygre... (AT) yahoo (DOT) com> wrote:
have you checked out SQL Dependency Tracker?

On Jan 22, 5:28 pm, Alex Kuznetsov <alk... (AT) gmail (DOT) com> wrote:
Thanks for the ideas. I'm going to try this SQL Dependency Tracker
too.
See they have a trial on their site.

kellygreer1