Query to list SQL Server stored procedures along with lines of code for each procedure

Question

I want a query that returns a list of all the (user) stored procedures in a database by name, with the number of lines of code for each one.

i.e.

sp_name     lines_of_code
--------    -------------
DoStuff1    120
DoStuff2    50
DoStuff3    30

Any ideas how to do this?

Answer 1

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

Edited so it should also now work in SQL Server 2000- 2008 and to exclude Database Diagram-related sprocs and funcs (which appear like user created objects).