Welcome to EMC Consulting Blogs Sign in | Join | Help

Third Abnormal Form

Resolution to SQL Server problems. From the front line.

Find the Total Lines of T-SQL Code in a SQL Database

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

Note: The code is not perfect because it counts the number if newlines and you could have a number of these with no text on them and these will be added to the count line total. Therefore you need to eliminate any lines with just whitespace from them to get the "real" line count value. Perhaps I will have a go at doing this in the future.

The code to run this is below 

and it is called via 

EXEC dbo.esp_GetObjectNumberOfRows

@DatabaseName = 'master',

@GroupingType = 'N',

@ExecuteSQL = 1,

@PrintSQL = 0;

 

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

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

*/

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.LinesOfCode) - 1 AS TotalLinesOfCode

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,

o.name AS ObjectName,

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

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

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 12:06 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

 

Ehrar said:

This is amazing.. save me so much time. Thanks.

August 21, 2014 6:00 PM

Leave a Comment

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