26

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?

Simon D
  • 4,150
  • 5
  • 39
  • 47
  • How are you counting "lines"? Arbitrary number of characters, number of linefeed/carriage-returns, or what? – Kevin Fairchild Nov 14 '08 at 22:09
  • Linefeed / carriage returns - kind of like you would see in Mgt Studio when you wrote the CREATE PROC for the proc. But it doesn't need to be 100% accurate. – Simon D Nov 16 '08 at 23:28
  • Just good enough to get a rough idea where the complexity is in a database. – Simon D Nov 16 '08 at 23:28

4 Answers4

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

Gordon Bell
  • 13,337
  • 3
  • 45
  • 64
  • This doesn't seem to work - it retrieves multiple different results for the same stored procedure. – Simon D Nov 16 '08 at 23:34
  • Fred, I fixed it to handle the duplicate stored procedure problem. Multiple syscomments rows are used for large stored procedures. – Gordon Bell Nov 17 '08 at 15:48
  • I am getting no result from the above code. I am using SQL2K8 EXPRESS edition. (I am looking for a query to get FUNCTION names, input and output parameters.) I don't see LTRIM, LEFT, etc function name if I just query sys.all_objects – Mehdi Anis Apr 27 '12 at 20:54
  • In Sql Server 2008 I needed to change the last 2 lines of code to: 'group by t.sp_name, t.type_desc, t.lines_of_code order by t.lines_of_code desc' – pat capozzi Aug 27 '13 at 16:00
11

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]
Cade Roux
  • 88,164
  • 40
  • 182
  • 265
2

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
DJ.
  • 16,045
  • 3
  • 42
  • 46
  • 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. – Simon D Nov 16 '08 at 23:36
  • You could easily change the PRINT command to an insert into another table. – DJ. Nov 17 '08 at 16:43
-3
select * from sysobjects where type = 'p'
fancyPants
  • 50,732
  • 33
  • 89
  • 96
Hari Prasad Savaravilli
  • 1