查询以列出 SQL Server 存储过程以及每个过程的代码行 [英] Query to list SQL Server stored procedures along with lines of code for each procedure
本文介绍了查询以列出 SQL Server 存储过程以及每个过程的代码行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想要一个查询,它按名称返回数据库中所有(用户)存储过程的列表,以及每个存储过程的代码行数.
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.
即
sp_name lines_of_code
-------- -------------
DoStuff1 120
DoStuff2 50
DoStuff3 30
任何想法如何做到这一点?
Any ideas how to do this?
推荐答案
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
经过编辑,现在它也应该在 SQL Server 2000-2008 中工作,并排除与数据库图相关的 sproc 和 funcs(看起来像用户创建的对象).
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 存储过程以及每个过程的代码行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文