USE master
go
IF EXISTS(SELECT * FROM sysobjects WHERE id = object_id('sp_getsprowcount') and objectproperty(id,'IsProcedure')=1)
	drop proc sp_getSProwcount
go
CREATE procedure sp_getSProwcount @ProcName varchar(128)=NULL
as
-------------------------------------------------------------------
-- Author: Neil Mederich
--   Date: 2/6/2002
--Purpose: Provide row counts for stored procedures
--Remarks: Will not provide row count for encrypted procedures
-------------------------------------------------------------------
SET nocount on 

DECLARE @strSQL varchar(350)
DECLARE @dbname sysname

CREATE TABLE #spcount (sptext varchar(255)) 

IF @ProcName IS NOT NULL
   BEGIN
		IF object_id(@ProcName) is null
		        BEGIN
				SELECT @dbname = db_name()
				RAISERROR(15009,-1,-1,@ProcName,@dbname)
		                RETURN (1)
		        END

		SET @strSQL = 'insert #spcount EXECUTE sp_helptext ''' + @ProcName + ''''
		EXECUTE(@strsql)

        SELECT @ProcName as spname,
               count(*) as [rows]
          FROM #spcount

        RETURN(0)	
   END

SELECT b.[name] + '.' + a.[name] as spname,
       0 as [rows]
  INTO #one
  FROM sysobjects a INNER JOIN sysusers b
    ON a.uid = b.uid
 WHERE xtype = 'p' 



DECLARE @x varchar(257)
DECLARE @count int 

DECLARE cone CURSOR FOR
 SELECT spname 
   FROM #one 

OPEN cone 

FETCH NEXT FROM cone INTO @x 

WHILE @@FETCH_status=0
  BEGIN
    TRUNCATE TABLE #spcount
    SET @strSQL = 'insert #spcount EXECUTE sp_helptext ''' + @x + ''''
    EXECUTE(@strsql)
    SELECT @count = count(*) FROM #spcount
    UPDATE #one SET [rows] = @count WHERE spname = @x
    FETCH NEXT FROM cone INTO @x  
  END
CLOSE cone
DEALLOCATE cone 

SELECT * FROM #one 
go