469,955 Members | 2,494 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,955 developers. It's quick & easy.

removing unused objects - SQL Server 2000 or 2005

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
ke*********@nospam.com
change nospam to yahoo

Jan 22 '08 #1
4 5497
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" <ke*********@yahoo.comwrote in message
news:8f**********************************@v46g2000 hsv.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
ke*********@nospam.com
change nospam to yahoo

Jan 22 '08 #2
Uri Dimant (ur**@iscar.co.il) writes:
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.
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, es****@sommarskog.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
Jan 22 '08 #3
On Jan 21, 6:36 pm, kellygreer1 <kellygre...@yahoo.comwrote:
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
kellygre...@nospam.com
change nospam to yahoo
have you checked out SQL Dependency Tracker?
Jan 22 '08 #4
On Jan 22, 5:28 pm, Alex Kuznetsov <alk...@gmail.comwrote:
On Jan 21, 6:36 pm,kellygreer1<kellygre...@yahoo.comwrote:
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
kellygre...@nospam.com
change nospam to yahoo

have you checked out SQL Dependency Tracker?
Thanks for the ideas. I'm going to try this SQL Dependency Tracker
too.
See they have a trial on their site.

kellygreer1
Jan 23 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by datapro01 | last post: by
reply views Thread by =?Utf-8?B?RENvdW50MTc=?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.