Welcome to EMC Consulting Blogs Sign in | Join | Help

Third Abnormal Form

Resolution to SQL Server problems. From the front line.

A way to find the Real Total Lines of T-SQL Code in a SQL Database

Version 2

 In my previous post 

http://consultingblogs.emc.com/paulmcmillan/archive/2012/01/03/find-the-total-lines-of-t-sql-code-in-a-sql-database.aspx

I said that the code was not perfect because it just counts the number of Line Feeds 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 from the actual line count. I said that perhaps 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
 

The Version 2 of this code is below....

 

CREATE PROCEDURE [dbo].[esp_GetObjectNumberOfRows]

(

@DatabaseName SYSNAME = '',

@GroupingType NCHAR (1) = 'N', -- N = Object Name, T = Object Type, D = Database

@ExecuteSQL BIT = 1, -- Default Execute SQL On

@PrintSQL BIT = 0 -- Default Debug SQL Off

)

/*

Paul McMillan

EMC Consulting 2012

Version 1

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

http://stackoverflow.com/questions/291574/query-to-list-sql-server-stored-procedures-along-with-lines-of-code-for-each-pro

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

Version 2

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

*/

AS

BEGIN;

IF @DatabaseName = ''

SET @DatabaseName = DB_NAME(); -- Default is current database

DECLARE @SQLMessage NVARCHAR (MAX);

-- Testing Code

--DECLARE @DatabaseName SYSNAME = 'master';

--DECLARE @GroupingType NCHAR (1) = 'T'; -- N = Object Name, T = Object Type, D = Database

SET @SQLMessage = '

SELECT

' + NCHAR (39) + @DatabaseName + NCHAR (39) + ' AS DatabaseName,

' +

CASE WHEN @GroupingType = 'N' THEN 'T.ObjectName, ' ELSE '' END +

CASE WHEN @GroupingType IN ('N', 'T') THEN '

CASE WHEN T.ObjectDescription = ''Stored Procedure''

THEN SUM(T.ProcedureCount)

ELSE SUM(T.FunctionCount)

END AS ObjectCount,' ELSE 'SUM(T.ProcedureCount) + SUM(T.FunctionCount) AS ObjectCount, ' END +

CASE WHEN @GroupingType IN ('N', 'T') THEN 'T.ObjectDescription, ' ELSE '' END + '

SUM(T.ActualLinesOfCode) - 1 AS TotalActualLinesOfCode,

SUM(T.RealLinesOfCode) - 1 AS TotalRealLinesOfCode

FROM

(

SELECT

CASE WHEN o.type = ''P'' THEN 1 ELSE 0 END AS ProcedureCount,

CASE WHEN o.type IN (''FN'', ''IF'', ''TF'') THEN 1 ELSE 0 END AS FunctionCount,

s.name + ''.'' + o.name AS ObjectName,

(LEN(C.definition) - LEN(REPLACE(C.definition, NCHAR(10), ''''))) AS ActualLinesOfCode,

(LEN(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(C.definition, '' '', ''''), NCHAR(9), ''''), NCHAR(13), ''''), NCHAR(10) + NCHAR(10), NCHAR(10)), NCHAR(10) + NCHAR(10), NCHAR(10))) - LEN(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(C.definition, '' '', ''''), NCHAR(9), ''''), NCHAR(13), ''''), NCHAR(10) + NCHAR(10), NCHAR(10)), NCHAR(10) + NCHAR(10), NCHAR(10)), NCHAR(10), ''''))) AS RealLinesOfCode,

CASE WHEN o.type = ''P'' THEN ''Stored Procedure'' -- STORED_PROCEDURE

WHEN o.type IN (''FN'', ''IF'', ''TF'') THEN ''Function'' -- SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, TABLE_FUNCTION

END AS ObjectDescription

FROM ' + @DatabaseName + '.sys.objects o

INNER JOIN ' + @DatabaseName + '.sys.sql_modules c

ON c.object_id = o.object_id

INNER JOIN ' + @DatabaseName + '.sys.schemas s

ON o.schema_id = s.schema_id

WHERE o.type in (''P'', ''FN'', ''IF'', ''TF'')

) AS T

' +

CASE WHEN @GroupingType IN ('N', 'T') THEN 'GROUP BY ' ELSE '' END +

CASE WHEN @GroupingType = 'N' THEN 'T.ObjectName, ' ELSE '' END +

CASE WHEN @GroupingType IN ('N', 'T') THEN 'T.ObjectDescription' ELSE '' END + '

ORDER BY 1' + CASE WHEN @GroupingType IN ('N', 'T') THEN ', T.ObjectDescription DESC' ELSE '' END

+ CASE WHEN @GroupingType IN ('N') THEN ', T.ObjectName ASC' ELSE '' END + ';';

IF @PrintSQL = 1

PRINT @SQLMessage;

IF @ExecuteSQL = 1

EXEC (@SQLMessage);

END;

Published Tuesday, January 03, 2012 2:42 PM by Paul.McMillan

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Matt Dixon said:

So helpful.  Thanks!

February 20, 2014 1:12 AM

Leave a Comment

(required) 
(optional)
(required) 
Submit
Powered by Community Server (Personal Edition), by Telligent Systems