resueltos - procedimiento almacenado sql server select

Consulta para enumerar los procedimientos almacenados de SQL Server junto con las líneas de código para cada procedimiento

Question

Quiero una consulta que devuelva una lista de todos los procedimientos almacenados (usuario) en una base de datos por nombre, con el número de líneas de código para cada uno.

es decir

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

¿Alguna idea de como hacer esto?

Answer 1

Esto funciona para 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

Answer 2

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

Editado por lo que ahora también debería funcionar en SQL Server 2000-2008 y excluir sprocs y funcs relacionados con el Diagrama de Base de Datos (que aparecen como objetos creados por el usuario).

Answer 3

FWIW, aquí hay otro:

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]

Answer 4

select * from sysobjects where type = ''p''