Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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?

like image 514
Simon D Avatar asked Nov 14 '08 21:11

Simon D


People also ask

How do I view a stored procedure in SQL Server query?

Using SQL Server Management StudioExpand Stored Procedures, right-click the procedure and then select Script Stored Procedure as, and then select one of the following: Create To, Alter To, or Drop and Create To. Select New Query Editor Window. This will display the procedure definition.


2 Answers

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

like image 136
Gordon Bell Avatar answered Sep 21 '22 08:09

Gordon Bell


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] 
like image 38
Cade Roux Avatar answered Sep 19 '22 08:09

Cade Roux