SQL Server SP, Function, View source line counter

Question

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

Answer 1

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...

Answer 2

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