Advertisements
Home > SQL, SQL Server > Count number of rows in stored procedure

Count number of rows in stored procedure

              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

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: