Find unused stored procedures in code?

Question

Is there an easier way of cleaning up a database that has a ton of stored procedures that I'm sure there are some that aren't used anymore other than one by one search.

I'd like to search my visual studio solution for stored procedures and see which ones from the database aren't used any longer.

Answer 1

Hmmm... you could search your solution for code which calls a stored proc, like this (from the DAAB)

    using (DbCommand cmd = DB.GetStoredProcCommand("sp_blog_posts_get_by_title"))
    {
        DB.AddInParameter(cmd, "@title", DbType.String,title);

        using (IDataReader rdr = DB.ExecuteReader(cmd))
            result.Load(rdr);
    }

Search for the relevant part of the first line:

DB.GetStoredProcCommand("

Copy the search results from the "find results" pane, and compare to your stored proc list in the database (which you can generate with a select from the sysObjects table if you're using SQL Server).

If you really want to get fancy, you could write a small app (or use GREP or similar) to perform a regex match against your .cs files to extract a list of stored procedures, sort the list, generate a list of stored procs from your database via select from sysobjects, and do a diff. That might be easier to automate.

UPDATE Alternatively, see this link. The author suggest setting up a trace for a period of a week or so and comparing your list of procs against those found in the trace. Another author suggested: (copied)

-- Unused tables & indexes. Tables have index_id’s of either 0 = Heap table or 1 = Clustered Index

SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID), INDEXNAME = I.NAME, I.INDEX_ID

FROM SYS.INDEXES AS I

INNER JOIN SYS.OBJECTS AS O

ON I.OBJECT_ID = O.OBJECT_ID

WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1

AND I.INDEX_ID

NOT IN (SELECT S.INDEX_ID

FROM SYS.DM_DB_INDEX_USAGE_STATS AS S

WHERE S.OBJECT_ID = I.OBJECT_ID

AND I.INDEX_ID = S.INDEX_ID

AND DATABASE_ID = DB_ID(db_name()))

ORDER BY OBJECTNAME, I.INDEX_ID, INDEXNAME ASC

which should find objects that haven't been used since a specified date. Note that I haven't tried either of these approaches, but they seem reasonable.

Answer 2

You could create a list of the stored procedures in the database. Store them into a file.

SELECT *
FROM sys.procedures;

Then read that file into memory and run a large regex search across the source files for each entry. Once you hit the first match for a given stored procedure, you can move on.

If there are no matches for a given stored procedure, you probably can look more closely at that stored procedure and may even be able to remove it.

I'd be careful removing stored procedures - you also need to check that no other stored procedures depend on your candidate for removal!

Answer 3

I would use the profiler and set up a trace. The big problem is tracking SPs which are only used monthly or annually.

Anything not showing up in the trace can be investigated. I sometimes instrument individual SPs to log their invocations to a table and then review the table for activity. I've even had individual SPs instrumented to send me email when they are called.

It is relatively easy to ensure that an SP is not called from anywhere else in the server by searching the INFORAMTION_SCHEMA.ROUTINES or in source code with GREP. It's a little harder to check in SSIS packages and jobs.

But none of this eliminates the possibility that there might be the occasional SP which someone calls manually each month from SSMS to correct a data anomaly or something.