EMC Consulting 2012
My Project Manager asked me to find out 'How many lines of code have we in the System'. I could not
believe that no one has ever had to do this before so I googled 'sql server stored procedure number of lines' and found
the following posting from Gordon Bell (many thanks!) on Stack Overflow in November 2008. See link below
However, the solution Gordon provided did not allow you to pass in a DatabaseName as a Parameter (and I had to find this
out for all the databases in a system) - I could have run it in each database individually but I thought I would take
the base code and allow a DatabaseName to be passed in as a parameter.
Furthermore, I thought I would update the code to use the latest SQL sys.objects and sys.sql_modules system tables
rather than older sysobjects ans syscomments system tables.
Additionally, I thought I would add a Grouping Parameter, so you can return either the
1) Total Lines of Code for each Stored Procedure and each Function for any Database. GroupingType = 'N' - Default
2) Total Lines of Code for all Stored Procedures and all Functions for any Database. GroupingType = 'T' - Default
3) Total Lines of Code for all Stored Procedures and Functions for any Database . GroupingType = 'D'
Finally as this is written using Dynamic SQL I have a Switches for Print SQL (Debug) and Execute SQL
I said that the code was not perfect because it just counts the number if newlines and you could have a number of
lines which do not have any text and as such these will be added to the count line total. Therefore you need to
eliminate any lines with just "whitespace" to get the "real" line count value. I said that prehaps I would
have a go at doing this in the future...
Well I realised that this was quiet easy to do, by extending the logic
The logic of the code is to get the total length of text and also the total length of text with all the Line Feeds removed
NCHAR(10). The difference minus 1 is the Number of Lines in the Text.
To eliminate all Whitespace lines (those with no code in) which could consist of any or all of the following
Spaces Characters NCHAR (32)
Tab Characters NCHAR (9)
Carriage Returns NCHAR (13)
Corrupted Carriage Return and Line Feed Lines NCHAR (13) + NCHAR (10) + NCHAR (10) - I have seen instances of this
I would need to remove these instances of 'Noise' from the text first and then find the total length after removing the Line Feeds from what remained after the noise was removed.
I have also added in the Schema Name to the Object Name as the initial version did not have this information and I noticed
it was possible to skew the results if Objects had the same name but in different Schemas