All geek questions in one place

How to find the name of the longest stored procedure?

I have a database containing 100 stored procedures. I want to find which one is the longest stored procedure. How can I find him?

I am currently using

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

But I cannot find the name of the stored procedure in it.

Thanks in advance:)

+3
source share
1 answer

sys.commentsdivides long definitions into 4000 characters. Use sys.sql_modulesto avoid this problem.

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
+8
source

Source: https://habr.com/ru/post/1762885/

More articles:


All Articles