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

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?

From stackoverflow

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

  Simon D : This doesn't seem to work - it retrieves multiple different results for the same stored procedure.

  Gordon Bell : Fred, I fixed it to handle the duplicate stored procedure problem. Multiple syscomments rows are used for large stored procedures.

  Simon D : Thanks GB, that's exactly what I need.

• This works for MS-SQL 2000

  SET NOCOUNT ON
  
  DECLARE @ProcName varchar(100)
  DECLARE @LineCount int
  
  DECLARE C CURSOR LOCAL FOR
      SELECT o.name as ProcName FROM sysobjects o WHERE (o.xtype = 'P') ORDER BY o.name
  
  OPEN C
  
  CREATE TABLE #ProcLines ([Text] varchar(1000))
  
  FETCH NEXT FROM C INTO @ProcName
  
  WHILE @@FETCH_STATUS = 0 
  BEGIN
  
      DELETE FROM #ProcLines
      INSERT INTO #ProcLines EXEC('sp_helptext ' + @ProcName + '')
  
      SELECT @LineCount = COUNT(*) FROM #ProcLines
  
      PRINT @ProcName + '   Lines: ' + LTRIM(STR(@LineCount))
  
      FETCH NEXT FROM C INTO @ProcName
  
  END
  
  CLOSE C
  
  DEALLOCATE C
  
  DROP TABLE #ProcLines
  

  Simon D : This is more like it - the sp_helptext means we're pretty close to what QA would give back. But it needs to go in a table to be really useful.

  DJ : You could easily change the PRINT command to an insert into another table.

  Simon D : Thanks DJ, that's a clever way to do it.

• FWIW, here's another one:

  SELECT  o.type_desc AS ROUTINE_TYPE
         ,QUOTENAME(s.[name]) + '.' + QUOTENAME(o.[name]) AS [OBJECT_NAME]
         ,(LEN(m.definition) - LEN(REPLACE(m.definition, CHAR(10), ''))) AS LINES_OF_CODE
  FROM    sys.sql_modules AS m
  INNER JOIN sys.objects AS o
          ON m.[object_id] = o.[OBJECT_ID]
  INNER JOIN sys.schemas AS s
          ON s.[schema_id] = o.[schema_id]
  

• select * from sysobjects where type = 'p'