Search Code in Project for Stored Procedures in the Database
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 retur开发者_运维百科n 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.
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.
use a consistent naming convention, like usp_
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.
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.
精彩评论