Big Data, Data Analytics and Data Science | BI-BIGDATA.com

              Many a times in our daily life of a SQL development/DBA profile, we have to spend our day across stored procedures, and we need to find out how many rows are there in any stored procedures, especially when we have to do some analysis or while  we have to review any stored procedure for evaluation or estimation, the very first thing which comes in everyone’s thought is, how big is the stored procedures ,  the following are the simple ways to get the approximate number of rows in stored procedure.

Lets create a sample stored procedure.

CREATE PROCEDURE A AS
BEGIN
   SELECT GETDATE()
END

The following is simple query to get number of rows in above stored procedure.

SELECT
    SP.NAME 'SP NAME',
    SUM((LEN(SC.TEXT) - LEN(REPLACE(SC.TEXT, CHAR(10), '')))) AS 'LOC'
FROM SYS.PROCEDURES SP
    INNER JOIN SYSCOMMENTS SC ON SP.OBJECT_ID=SC.ID
WHERE
   SP.OBJECT_ID=OBJECT_ID('A')
GROUP BY
   SP.NAME

The following is result.

The following query is used to retrieve the biggest SP (Based on LOC – Lines of code)

SELECT
    SP.NAME 'SP NAME',
    SUM((LEN(SC.TEXT) - LEN(REPLACE(SC.TEXT, CHAR(10), '')))) AS 'LOC'
FROM SYS.PROCEDURES SP
    INNER JOIN SYSCOMMENTS SC ON SP.OBJECT_ID=SC.ID
GROUP BY
    SP.NAME
ORDER BY
    LOC DESC

Thanks
Manish