September 11, 2010

Find Unused Objects in your SQL Server Database




Here’s a disclaimer - There is no easy way of doing this!

Here are a couple of options that you can use to find unused objects in your database:


1. One option is to run the profiler, capture the results and analyze if any objects are used at all. This should be done for a certain period of time. If any application is connected to the database, run each and every functionality available in front end and, capture and analyze the profiler result.


2. Another method is to rename certain objects that you think are not being used and observe the logs over a period of time and check for any code breaks. This is based on a trial-and-error method.


3. Another alternative is via a query

SELECT source_code,last_execution_time
FROM sys.dm_exec_query_stats as stats
CROSS APPLY (
SELECT text as source_code
FROM sys.dm_exec_sql_text(sql_handle))
AS query_text
ORDER BY last_execution_time desc




unused_objects

This query wont give you a list of unused objects. It gives you a list of used objects which you need to keep track for some time and see if any of the objects are not used. It is like monitoring trace result for sometime to determine if any objects are not used.


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan

 
  Feedback:

comments

0 Responses to "Find Unused Objects in your SQL Server Database"
 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions