Stored procedure result table count

Question

I would like to find out how many tables are returned by each of a large number of undocumented stored procedures.

Is there any way that I can get this information programatically? Are there any statistics tools that might do this for me?

Edit - to be clear, I'm looking for the number of result sets not the number of referenced tables.

Answer 1

The fatal flaw is that the number of result sets returned can be variable based on the logic in the stored procedure.

IF XXXX BEGIN
SELECT xxxx
SELECT xxxx
END
ElSE
BEGIN
SELECT xxxx
SELECT xxxx
SELECT xxxx
SELECT xxxx
END

Side note - if I had written the stored procedures the answer would always be zero or one. I've never found a good design pattern that implemented multiple result sets from one query - it inevitably violates KISS.

Answer 2

SET FMTONLY ON

Will probably do what you need.

But this has some limitations - related to temp table usage (the same problems that LINQ and SSIS have when trying to determine what some SPs do).

Remember, there is no 100% reliable way to determine this, since SPs contain code which could mean that the number of result sets is arbitrary (especially if they do a cursor to return subordinate detail result sets based on an initial master result set).

Answer 3

Is there any way that I can get this information programatically?

Short answer No, in SQL server there is no way to get the number of result sets a proc returns because the number of results sets returned by a stored proc can be dynamic and can depend on the values of the parameters passed to the stored procs.

Some stored procs may error out if they receive the wrong inputs.

With that in mind:

• In pure TSQL there is no way to determine the number of result sets a proc returns, farther more there is no way to capture more than 1 result set using the insert exec pattern

• You could probably fangle something up with SQL CLR that counts the result sets. (You will have to execute the proc with dummy params and rolls back a tran)

• You could write something in C# or any other client tool that, creates a tran, injects dummy params, executes, counts the result sets and rolls back the transaction.

Answer 4

My concern here is that he's asking for the number of tables returned by the stored procedures. If a stored procedure returns the results of a SQL select statement, it may very well return data from multiple tables. In that case, number of resultsets is not equal to the number of tables.

Any tool that would calculate the number of tables returned by the stored procedures would have be very clever, as it would likely have to parse the stored procedure's code if it didn't want to have to execute them outright. It gets even more complicated if the stored procedure invokes functions, or is written in a .NET language.

Some of the data might be encapsulated in views, which would further dereference the data (possibly even further through other views). And (as we all must reluctantly admit) some stored procedures are just badly written.

EDIT: It's interesting that this answer keeps getting voted down. It was written when the question was first posted, before he'd clarified his question. At the time, my concerns were valid. I don't know if they led to his clarifying his question, but it's thought-provoking that folks are eager to downvote an answer without providing a comment to justify the downvote.

Answer 5

under DB2:

select result_sets from syscat.procedures where procname='...'

gives the estimated number of returned tables.

Answer 6

You could build a classic ADO wrapper to call the sprocs and count the number of returned recordsets (NextRecordSet method). Iterate through them (using a data dictionary query if necessary to get a list) and see. In the absence of any other suitable tools you could even do this in VBA from Excel. Download and install an appropriate version of MDAC if you don't have ADO libraries installed on the system.