**Stored Procedure Stats **Download Exe Stored Procedure Stats is a simple Windows Forms 2.0 Beta 2 application written in C# that you can use to:
- Get a list of all the stored procedures in your SQL Server (only) database
- See the actual code in your stored procedures
- Search the contents of your stored procedures for any keywords
You can change the connection string to anything you’d like. By default it uses Integrated Security to connect to Northwind.
**List Stored Procedures **Clicking the List Stored Procedures button queries the sysobjects table in the given database for any user stored procedures using the following query:
- select name from sysobjects where xtype='P’ and category <> 2
Calling this code on the Northwind database fills a CheckedListBox with all the stored procs in NorthWind.
**View Stored Procedure **The View Stored Procedure button returns the actual stored procedure code for the the selected stored procedures by using the SQL Server built-in sp_helptext stored procedure.
Get Total Lines of Code This button gets the text for all the selected stored procedures and counts the total lines of code and the average # of lines of code per stored procedure. For Northwind and Pubs, the results are:
- Northwind Total Lines of Code: 62
- **Pubs Total Lines of Code: **36
- Northwind Average LOC per Stored Proc: 8
- Pubs Average LOC per Stored Proc: 9
**Search Stored Procedures **The search Stored Procedures (pic below) is a very handy feature for searching the code in your stored procedures. This idea is actually from Jonathan Barrett, my old manager during my .com days. He created a Web app that searched stored procedures as well as client code, which made it really easy to find reusable code(ex: datetime parsing, substring/string functions,etc), bad code (ex: cursors) or dependencies (before you change that column name, you can instantly see all the stored procs that will be affected). This sample only searches stored procs and not client code, but I’d love it if someone could extend this to also search client code.
Searching for the term “Select” in Northwind shows us that it is used 8 times in multiple stored procedures. The result set shows the stored procedure name, line number, and the actual line of code as shown below.
*** 8 Matches Found Match: 1 of 8 Stored Proc Name: CustOrderHist Line Number: 3 Line: SELECT ProductName, Total=SUM(Quantity)
Match: 2 of 8 Stored Proc Name: CustOrdersDetail Line Number: 4 Line: _SELECT ProductName, _…
Here are some stats from Northwind, everyone’s favorite database
- “=”: 19 matches
- “ID”: 15 matches
- “@” 12 matches (good indicator of how many variables you have)
- “Orders” 10 matches
- “Select”: 8 Matches
- “Convert” 4 matches
- “and” 6 matches
- “Customer” 6 matches
- “round” 3 mtaches
- “sum” 2 matches
- “substring” 1 match
- “!=” 1 match
Features used in this sample
- Property Binding
- Regular Expressions (for Search Operation)
- Generics (everywhere)
- Background Worker (for the Search operation) - Code “leveraged” from my GoogleImages sample
Features that didn’t quite make it in
- Custom AutoComplete Provider a la Google Suggest
- Match Case for text searching
- Non-SQL Server support
- Total Lines of Code: 2100
- Average LOC per Stored Proc: 33
- “Select” statements: 228
- “@” symbol: 508
Now that I’ve thoroughly wasted my time writing code to help add a single bullet item in my review, I need to actually go write my review!