Stored Procedure Utility – Count Lines of Code and Search Contents


<backstory to get motivation on why I did this>
It’s review time here for Microsoft employees, and I won’t bore you with my thoughts on the review process as many in the blogosphere have written about it. I need to start writing my review and I wanted to get some high-level statistics on what I’ve done from a development perspective. Why am I writing code? Because I’m a sucker 🙂
Somewhere around six weeks before Beta 2, we lacked a mechanism for mailing customers who registered emails with their registration benefits and any capability to report on who’s registering Beta 1 or Beta 2. Since I had personally worked on some of the details on getting data into and out of our user database, MSI (MicroSoft Individual), I stupidly volunteered (and sucked Tom Krueger and Scott Currie in) to take this work on b/c I didn’t want to have our customers have a bad experience. Back to my review, I wanted to get the total lines of code for the app and DB code. Scott Currie had a nice C# application that counted the lines of code in the project, but there wasn’t away to track how much code was actually in the stored procedures. So rather then actually spend time writing my review, I wrote code to count the lines of database code I wrote. With that I give you…..
</backstory>


Stored Procedure Stats
Download Exe || Download Source
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
  • Colorization
  • Non-SQL Server support

<back to the backstory>
So I ran this puppy on one of our two databases, unchecked the ASP.NET 2.0 stored procs (for SQLDependency code), and the results are:



  • 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!
</backstory>


 


Comments (22)

  1. Joe says:

    Really NICE!

    I always wondered how many lines of code there were…

    Sincere thanks for sharing

    Best Regards,

  2. chris says:

    brilliant!

    exactly what i was looking for.

    many thanks mate!

  3. Ben Sullins says:

    Great app…very usefull…I was about to write a stored proc to do the same thing but from the Query Analyzer interface…If I do I will post it on my site for users to download

    Cheers!

    =======

    Ben Sullins

  4. T.J. Regan says:

    Excellent utility. I’ve written enough SP’s so that when I need to solve a ‘new’ problem, I think: "Didn’t I do this before?".

    This just saved me a bunch of time and work.

    Thank you, Dan.

  5. Doug says:

    Thanks so very much……

    I wonder why Microsoft did not incorporate this type of

    search engine into SQL 2005……..

    This is a blessing for finding dependencies of

    nested stored procedures and every other code

    change I need to make.   I now have a sense of security that I will not break something unknowingly.

    Now I just need to expand this to search all code files and

    dll files on the web servers……

    But this little utility solved 90% of the problem…….old

    code that was never properly documented or maintained.

  6. It’s about time I added a contribution to the blogosphere lexicon so you too can seem l33t at the next…

  7. Matt says:

    We could extent it to search Views and Functions as well because these might have columns and table names that you could break…

    Thinking that we could just change the stored proc search sql query to this….

    select * from sysobjects where

    (xtype=’P’ and category <> 2) –P = Stored procedure

    or (xtype=’FN’ and category <> 2) –FN = Scalar function

    or (xtype=’IF’ and category <> 2) –IF = In-lined table-function

    or (xtype=’TF’ and category <> 2) –TF = Table function

    or (xtype=’TR’ and category <> 2) –TR = Trigger

    or (xtype=’V’ and category <> 2) –V = View

  8. Lizy says:

    It would have been more helpful if it had an option of getting the count of lines for each of all or selected stored procedures separately (as now we have the sum of the count of all selected stored procs.)

  9. Marcel Isler says:

    I am about to release a tool to search content of table definitions, view defintions, functions, stored procedures and triggers.

    This is a full windows application that allows you to search for strings inside of all the database objects in all the databases in a server instance.  You can filter the search by database and object type, sort the result and so on.  The source code is shown in a syntax colored window and you can copy paste code out of there.  I’m planning to allow the user to automatically transfer all of the code or just the selection over to Query Analyzer for execution.

    You can find more information in my blog at http://sqlobjectsearch.blogspot.com and soon on my website http://www.sqlobjectsearch.com

  10. Kyle Finley says:

    I stumbled upon this post by Dan Fernandez earlier and thought it was pretty cool.  Just a simple winform…

  11. Phil Mulhall says:

    Dan,

    This utility is brilliant – it is *exactly* what I was looking for.

    Thanks

    Phil

    phil@xmulhall.eu

    (replace ‘x’ with ‘phil’)

  12. David says:

    Yep, thanks Dan.  Even though you wrote this over a year ago, it’s just what I needed today!

    Best-

    -David

  13. The Foz says:

    Thanks dude, this is a really cool feature, and It’s just what I need for a little task I’m doing now.

  14. Colin says:

    Hi,

    Pretty cool application! I’ve just got a query. How would I go about writing a stored proc or a piece of T-SQL Code that does that same thing as the application (if at all possible)?

    Al lot of my clients don’t have .NET 2.0 installed on their systems, so it’s not possible to run this app on their systems without installing .NET 2.0 (which they do not want done.)

    I know how to get the text out of the table that contains the actual text for the particular Stored proc, but the problem is that if you copy the text and paste it into let’s say Notepad, all of the text is displayed in a single line. Nowhere in this code that I copied could I find a delimiter that indicates a new line has started, so I’m pretty stuck.

    Your help would be greatly appreciated.

    Thanks

    Colin.

  15. Dean says:

    I tried to run the tool on a large amount of stored procedures. The tool works great when listing the stored procedures but returns the error ‘Timeout expired. The timeout period expired prior to obtaining a connection from the pool…’ when trying to get the total lines of code. Are there any parameters that I could set to get around this issue?

  16. L Johnson says:

    This is a little late for Dean.  However to get around the  "Timeout Period Expired priot to obtaining a connectin from the pool" error.  

    In the Utils.Cs  (StoredProcStats.DB) in the GetStoredProcInfo(string spName) method

    Replace these lines:

    while (dr.Read())

                   {

                       sb.Append((string)dr["text"]);

                       textList.Add((string)dr["text"]);

                   }

    With these lines:

     try

               {

                   while (dr.Read())

                   {

                       sb.Append((string)dr["text"]);

                       textList.Add((string)dr["text"]);

                   }

               }

               catch (Exception ex)

               {

                    //TODO:  Error handling here

               }

               finally

               {

                   if (!(dr == null) && !(dr.IsClosed))

                       dr.Close();

               }

    Basically, you are forcefully closing the connection.

  17. Vj says:

    It’s really awesome!! nice work!

  18. James says:

    Why would you count lines of code for a language like SQL?  There are no line formatting rules.  You could write a statement in one line or 25, depending on whether developers use verbose writing styles (one column per line, one where condition per line, etc) or abbreviated.

  19. HaggardPete says:

    Super little application. However If you just want  to  this from SQL –  or want all procedures independantly This should help:

    —————————————————————————————

    — Count SP lines POC May 2008

    —————————————————————————————

    SET NOCOUNT ON

    DECLARE @ProcName VARCHAR(512)

    DECLARE @Procid INTEGER

    DECLARE @lines INTEGER

    — temp table for procdure text

    CREATE TABLE #mt (t text)

    — Temp table for results

    CREATE TABLE #res (SP VARCHAR(512),lines INTEGER)

    DECLARE curProcs CURSOR  FOR

    SELECT [NAME],[id]  FROM sysobjects where xtype=’P’ AND category<>2

    OPEN curProcs

    FETCH NEXT FROM curProcs INTO @ProcName,@Procid

    WHILE  @@FETCH_STATUS=0

    BEGIN

    — Obtain the text of the operation and then count le number of lines

    TRUNCATE TABLE #mt

    INSERT INTO #mt exec sp_helptext @ProcName

    SELECT @lines=count(*) FROM #mt

    INSERT INTO #res (sp,lines) VALUES (@ProcName,@Lines)

    FETCH NEXT FROM curProcs INTO @ProcName,@Procid

    END

    CLOSE CurProcs

    DEALLOCATE CurrProcs

    SELECT * FROM #Res ORDER BY lines desc

    DROP TABLE #mt

    DROP TABLE #res

    —————————————————————————————

    — End count SP lines

    —————————————————————————————

  20. Yoganand.A says:

    This is one of the Fantastic tool. Mostl of the software size is measured with FP(Functional Point) or LOC(Lines of code).

    I have once clarifications. Whether the tool will count the all the lines?. Can i take only the executable lines (ignoring comments and blank lines only for Stored Procedures).

  21. Zoltan Csabai says:

    thanks folks this has been very useful, i have had to modify the SP code to work with multiple schemas, so i though you might like to see the changed code.

    —————————————————————————————

    — Count SP lines POC May 2008

    —————————————————————————————

    BEGIN TRAN

    SET NOCOUNT ON

    DECLARE @Schema VARCHAR(512)

    DECLARE @ProcName VARCHAR(512)

    DECLARE @CompleteProcName VARCHAR(1024)

    DECLARE @Procid INTEGER

    DECLARE @lines INTEGER

    — temp table for procdure text

    CREATE TABLE #mt (t text)

    — Temp table for results

    CREATE TABLE #res (SP VARCHAR(512),lines INTEGER)

    DECLARE curProcs CURSOR  FOR

    SELECT S.[Name] as [Schema], O.[NAME],O.[object_id]  

    FROM sys.objects O

    JOIN sys.schemas S

    ON S.Schema_ID = O.Schema_ID

    WHERE O.type=’P’

    OPEN curProcs

    FETCH NEXT FROM curProcs INTO @Schema, @ProcName,@Procid

    WHILE  @@FETCH_STATUS=0

    BEGIN

    — Obtain the text of the operation and then count le number of lines

    TRUNCATE TABLE #mt

    set @CompleteProcName = @Schema + ‘.’ + @ProcName

    INSERT INTO #mt exec sp_helptext @CompleteProcName

    SELECT @lines=count(*) FROM #mt

    INSERT INTO #res (sp,lines) VALUES (@ProcName,@Lines)

    –print @Schema + @ProcName

    –print ‘complete name = ‘ + @CompleteProcName

    FETCH NEXT FROM curProcs INTO @Schema,@ProcName,@Procid

    END

    CLOSE CurProcs

    DEALLOCATE CurProcs

    SELECT SP as ‘SP”s in # of Lines order’, Lines FROM #Res ORDER BY lines desc

    SELECT SP as ‘SP”s in alphabetical order’, Lines FROM #Res ORDER BY SP desc

    DROP TABLE #mt

    DROP TABLE #res

    COMMIT TRAN

    —————————————————————————————

    — End count SP lines

    —————————————————————————————

    –SELECT OBJECT_DEFINITION(OBJECT_ID(‘ctrl.RecordTableCreate_sp’)) AS body

    –SELECT OBJECT_DEFINITION(OBJECT_ID(‘daily.PreProcess_sp_SSIS’)) AS body

    –sp_helptext ‘history.CreateComputedColumns_Sp’

  22. Meganadha Reddy K. says:

    Really useful.

    Excellent work!!!

    Regards,

    Meganadha Reddy K.