查询列出SQL Server存储过程以及每个过程的代码行

即

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和函数(看起来像用户创建的对象).

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!