Search Code in Project for Stored Procedures in the Database

Question

Is there an easy way to run a search for all the stored procedures in the database to see if they are anywhere in C# code in Visual Studio?

I am using Visual Studio 2008 (C#) with SQL Server 2005. I just want to query the database and return a list of all the stored procedures (I can easily do this). Then I want to search through an entire project to find which stored procedures are being used in the code, and which ones are not.

(Ok, as I write this out, I have an idea, but it involves looking though each file one at a time, which ain't bad, but I wonder if there is a better solution).

EDIT: It seems my question was not clear enough. I want to get a list back of every stored procedure in the database that is not being called from code. I am trying to remove all the unused code and database objects, so I need to know what is not used, and then start deleting.

Answer 1

You can tick the "Use Regular Expressions" option in the "find in files" dialogue then separate the names with a | character.

To generate the pipe delimited list of procedure names to search for you can use

declare @list varchar(max)

select @list= isnull(@list + '|','') + name from sys.procedures

select @list AS [processing-instruction(x)] FOR XML PATH('')

To find stored procedures not referenced at all in your code you could look at a third party tool to do this such as Apex SQL Clean.

You might find that you can do all you need for now within the free trial period.

Answer 2

use a consistent naming convention, like usp_

Answer 3

Depends on how the code is written.

You might be able to do a search for something like .CommandText.

Other things to search for might be just SqlCommand or .CommandType = CommandType.StoredProcedure.

Hopefully you might then get out all the lines that contains the name of a SP and then you might be able to use a regex or similar to get out just the SP names from the lines.

Answer 4

I just created a program to grab all the stored procedure names from the database. I then read through all the files in my project manually, and check to see if they contained the stored procedure name. If the name was not found in any files, I added it to a list.

I was hoping to find an easier way to do this, but this works for what I needed.