查询以列出 SQL Server 存储过程以及每个过程的代码行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/291574/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query to list SQL Server stored procedures along with lines of code for each procedure
提问by Simon D
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.
IE
sp_name lines_of_code
-------- -------------
DoStuff1 120
DoStuff2 50
DoStuff3 30
Any ideas how to do this?
任何想法如何做到这一点？
回答by Gordon Bell
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).
已编辑，现在它也应该在 SQL Server 2000-2008 中工作，并排除与数据库图相关的 sprocs 和 funcs（看起来像用户创建的对象）。
回答by Cade Roux
FWIW, here's another one:
FWIW，这是另一个：
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]
回答by DJ.
This works for MS-SQL 2000
这适用于 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
回答by Hari Prasad Savaravilli
select * from sysobjects where type = 'p'
