1

Is there any utility availble to count the total lines of user created Stored Procedure, Function, Views in a Database?

Rajganesh Mountbatton
  • 47
  • 1
  • 6

2 Answers2

2

For SQL Server 2005 and 2008.

This includes all code including blank lines and trailing blank lines, but not the last line (no CRLF). So it's averages out... but it would always be an approximation anyway.

WITH CRLF AS
(
    SELECT
        CHARINDEX('
', definition) AS CRLF,
        SM.[object_ID]
    FROM
        sys.sql_modules SM
WHERE
    OBJECT_NAME([object_ID]) not in ('fn_diagramobjects', 'sp_alterdiagram', 'sp_creatediagram', 'sp_dropdiagram', 'sp_helpdiagramdefinition', 'sp_helpdiagrams', 'sp_renamediagram', 'sp_upgraddiagrams', 'sysdiagrams')
    UNION ALL
    SELECT
        CHARINDEX('
', definition, C.CRLF + 2),
        SM.[object_ID]
    FROM
        sys.sql_modules SM
        JOIN
        CRLF C ON SM.[object_ID] = C.[object_ID]
    WHERE
        CHARINDEX('
', definition, C.CRLF + 2) > C.CRLF
)
SELECT
    COUNT(*)
FROM
    CRLF
OPTION
    (MAXRECURSION 0)

Edit: You may need OBJECTPROPERTY(SM.[object_ID], 'IsMSShipped') = 0 or explicitly exclusions for diagram code etc

Edit 2:

From other solution in otehr answer, corrected to not give "-1" for check constraints and apply same filters/types

select t.sp_name, sum(t.lines_of_code) as lines_ofcode, t.type_desc
from
(
    select o.name as sp_name, 
    (len(c.text) - len(replace(c.text, char(13), ''))) 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 ('V', 'P', 'FN', 'IF', 'TF', 'TR')
    --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
COMPUTE SUM (sum(t.lines_of_code))

They all give the same results here on several databases. eg 4607 for a SQL Server 2005 SP2 ReportServer database...

gbn
  • 422,506
  • 82
  • 585
  • 676
  • somehow my solution is coming up with a diff answer to yours. – Sam Saffron Jul 13 '09 at 11:46
  • Thanks gbn & Sam for your replies. Tried out http://stackoverflow.com/questions/291574/query-to-list-sql-server-stored-procedures-along-with-lines-of-code-for-each-proc. All the three gives different results. Which one to consider? – Rajganesh Mountbatton Jul 13 '09 at 11:52
  • @Sam: one difference without excluding diagram filter! @Rajganesh: The other solution does not include triggers and views, type V and TR,. Remove the xtype filter and it's close. – gbn Jul 13 '09 at 12:01
  • ...but it also includes check constraints and gives "-1" rowcount for these – gbn Jul 13 '09 at 12:12
0

Not that I know of, but you could look through the stuff in sysobjects and execute sp_helptext on each proc and view and count the newlines.

If you want a non CTE based solution you could do something like this: 

select sum(newlines) from 
(
select newlines = (datalength(definition) - datalength(replace(definition, '
', ' '))) / 2 from sys.sql_modules
) as a
Sam Saffron
  • 128,308
  • 78
  • 326
  • 506