Partager via

Stored Procedure Utility - Count Lines of Code and Search Contents

  • Article

<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

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

  • Anonymous
    September 10, 2005
    Really NICE!

    I always wondered how many lines of code there were...

    Sincere thanks for sharing

    Best Regards,

  • Anonymous
    December 13, 2005
    brilliant!

    exactly what i was looking for.

    many thanks mate!

  • Anonymous
    January 10, 2006
    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

  • Anonymous
    April 06, 2006
    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.

  • Anonymous
    May 02, 2006
    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.



  • Anonymous
    June 07, 2006
    It’s about time I added a contribution to the blogosphere lexicon so you too can seem l33t at the next...

  • Anonymous
    July 03, 2006
    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

  • Anonymous
    July 19, 2006
    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.)

  • Anonymous
    August 09, 2006
    The comment has been removed

  • Anonymous
    August 30, 2006

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

  • Anonymous
    September 04, 2006
    Dan,

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

    Thanks
    Phil
    phil@xmulhall.eu
    (replace 'x' with 'phil')

  • Anonymous
    November 28, 2006
    Yep, thanks Dan.  Even though you wrote this over a year ago, it's just what I needed today! Best- -David

  • Anonymous
    January 02, 2007
    Thanks dude, this is a really cool feature, and It's just what I need for a little task I'm doing now.

  • Anonymous
    January 29, 2007
    The comment has been removed

  • Anonymous
    February 15, 2007
    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?

  • Anonymous
    May 10, 2007
    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.

  • Anonymous
    July 16, 2007
    It's really awesome!! nice work!

  • Anonymous
    April 28, 2008
    The comment has been removed

  • Anonymous
    May 06, 2008
    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

  • Anonymous
    July 21, 2008
    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).

  • Anonymous
    October 08, 2008
    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'

  • Anonymous
    June 30, 2009
    Really useful. Excellent work!!! Regards, Meganadha Reddy K.