我想要一个查询,返回数据库中所有(用户)存储过程的列表,其名称与每个代码的行数.
即
sp_name lines_of_code -------- ------------- DoStuff1 120 DoStuff2 50 DoStuff3 30
任何想法如何做到这一点？
解决方法
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','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中工作,并排除与数据库图相关的sprocs和函数(看起来像用户创建的对象).
