Finding Dead Objects in SQL Server 2008

I have been doing some house cleaning on my databases, looking for stored procedures that are no longer used by any of our applications. I’m not using any fancy third party tools, just what I think is a little common sense and some T-SQL queries.

The first step is to look for any obsolete or unused tables in the database. If you are certain that a table is no longer used, you can look for any objects that reference that table by querying sys.dm_sql_referencing_entities to find objects that depend on that table.

-- Get SPs that reference a particular table
SELECT referencing_entity_name AS 'SP Name'
FROM sys.dm_sql_referencing_entities ('YourSchemaName.YourTableName', 'OBJECT')

If you are less sure that a table is no longer being used, you can run the query below to find SPs that reference your table that are not in the procedure cache. Of course, just because it is not in the cache does not mean that is is not used, but it is less likely. It could be that the SP is not part of your normal workload, or that it is using WITH RECOMPILE or OPTION(RECOMPILE), so it is not in your cache.

    -- SPs that reference a particular table that are not in procedure cache
    -- (SQL Server 2008 Only)
    
    -- Get SPs that reference a particular table
    SELECT referencing_entity_name AS 'SP Name'
    FROM sys.dm_sql_referencing_entities ('YourSchemaName.YourTableName', 'OBJECT')
    
    EXCEPT
    
    -- Get list of all SPs from the current database
    -- that are in the procedure cache 
    SELECT p.name AS 'SP Name'        
    FROM sys.procedures AS p          
    INNER JOIN sys.dm_exec_procedure_stats AS qs
    ON p.object_id = qs.object_id
    WHERE p.is_ms_shipped = 0
    AND p.[type] = 'P';

You can easily reverse this query to find SP’s that are in cache that reference your table by changing EXCEPT to INTERSECT.

    -- SPs that reference a particular table that are in procedure cache
    -- (SQL Server 2008 Only)
    
    -- Get SPs that reference a particular table
    SELECT referencing_entity_name AS 'SP Name'
    FROM sys.dm_sql_referencing_entities ('YourSchemaName.YourTableName', 'OBJECT')
    
    INTERSECT
    
    -- Get list of all SPs from the current database
    -- that are in the procedure cache 
    SELECT p.name AS 'SP Name'        
    FROM sys.procedures AS p          
    INNER JOIN sys.dm_exec_procedure_stats AS qs
    ON p.object_id = qs.object_id
    WHERE p.is_ms_shipped = 0
    AND p.[type] = 'P';

This entry was posted in SQL Server 2008. Bookmark the permalink.

17 Responses to Finding Dead Objects in SQL Server 2008

  1. Dukagjin says:

    Very nice explanation…thnx for the post and all infos!Regards,Dugi

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s