SQL Server find stored procedures that return result sets

Question

In SQL Server, it is possible to declare stored procedures that return one or several result sets:

CREATE PROCEDURE dbo.proc1
AS 
SELECT 1 a, 2 b UNION SELECT 3 a, 4 b;

I'm looking into trying to detect all such stored procedures from the dictionary views in SQL Server 2008+. Unfortunately, I cannot seem to be able to find a distinctive flag identifying such a procedure in either:

• INFORMATION_SCHEMA.ROUTINES
• sys.procedures

Here's also a SQLFiddle, showing that there appears to be no difference in meta data between procedures returning result sets, and those not returning result sets.

Can it be done in a formally correct way (not looking for any hacks / tricks)? If so, how?

Answer 1

I don't think it is possible. In SQL Server a stored procedure can contain dynamic SQL that constructs queries (or calls to other stored procedures that - for example - return result sets). This isn't just limited to dynamic SQL though, for example having a SELECT in an IF will also decide whether a stored procedure returns 0 or 1 result sets. Any produced result set that isn't handled by the stored procedure itself is returned to the user.

So it is possible for a stored procedure to return an arbitrary number of results (counts and result sets). This means that at compile time (create or alter) it is not possible to determine if (and how many) result sets are actually produced, which is probably why you can't find this in the metadata.