Calculate lines of code in Sql Stored Procedure

Tip #3 Find the lines of code in Sql Stored Procedure.

I am posting some of the most useful queries which help a dba/ developer to optimize database performance. Please reference to different Tips in other blogs as well.

Following query will help in determining the lines of code present in the stored procedure. This query will exclude spaces and comments while calculating the lines of codes.. Run the following query in your sql server query window:

SELECT t.sp_name
 ,sum(t.lines_of_code) - 1 AS lines_ofcode
 ,t.type_desc
FROM (
 SELECT o.NAME AS sp_name
  ,(len(c.TEXT) - len(replace(c.TEXT, CHAR(10), ''))) AS lines_of_code
  ,CASE 
   WHEN o.xtype = 'P'
    THEN 'Stored Procedure'
   WHEN o.xtype IN (
     'FN'
     ,'IF'
     ,'TF'
     )
    THEN 'Function'
   END AS type_desc
 FROM sysobjects o
 INNER JOIN syscomments c
  ON c.id = o.id
 WHERE o.xtype IN (
   'P'
   ,'FN'
   ,'IF'
   ,'TF'
   )
  AND o.category = 0
  AND o.NAME NOT IN (
   'fn_diagramobjects'
   ,'sp_alterdiagram'
   ,'sp_creatediagram'
   ,'sp_dropdiagram'
   ,'sp_helpdiagramdefinition'
   ,'sp_helpdiagrams'
   ,'sp_renamediagram'
   ,'sp_upgraddiagrams'
   ,'sysdiagrams'
   )
 ) t
GROUP BY t.sp_name
 ,t.type_desc
ORDER BY 1