How to find the name of longest stored procedure ?

Question

Hello,

I have a database which contains 100's of stored procedures. I want to find which the longest stored procedure in these. How can i find it?

Currently i am using

Select text,MAX(len(text)) from syscomments group by text;

But i am not able to find the name of stored procedure in it.

Thanks in advance :)

Answer 1

sys.comments divides long definitions into 4000 character chunks. Use sys.sql_modules to avoid this issue.

SELECT TOP 1 
    OBJECT_NAME(object_id) AS Name, 
    LEN(definition) AS Length, 
    CAST((SELECT definition AS [processing-instruction(x)] FOR XML PATH('')) AS XML) AS Definition
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsProcedure')=1
ORDER BY LEN(definition) DESC