How can we get the full output of sp_help system stored procedure?

Question

Hi there, it is well documented the steps for a system stored procedure returning just a single dataset but in this case sp_help returns more than one

so in this example I am trying to get the data for the 'test' table

SELECT * INTO #infoTBL
FROM OPENROWSET('sqloledb', 'server=xxxxxxxxxx;trusted_connection=yes'
, 'exec sp_help ''test''')

Thanks in advance,

Answer 1

Hi,

Hi there, it is well documented the steps for a system stored procedure returning just a single dataset but in this case sp_help returns more than one

This claim is not accurate when you speak about system stored procedure in general. There are system stored procedures which return multiple result SET exactly as you mentioned in the case of sp_help.

Moreover, there are other system objects which work with multiple result SET retuned from SP, like for example sys.dm_exec_describe_first_result_set_for_object.

The limitation which you speak about is when using OPENROWSET specifically and not a general limitation. It is true that OPENROWSET returns only the first result SET even if the query return multiple result sets.

https://learn.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15&WT.mc_id=DP-MVP-5001699

but as I mentioned sys.dm_exec_describe_first_result_set_for_object get metadata of any returned SET from the SP that returns multiple SETs.

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-describe-first-result-set-for-object-transact-sql?view=sql-server-ver15&WT.mc_id=DP-MVP-5001699

;CREATE or alter Procedure TestingMultipleSET AS  
    SET NOCOUNT ON;  
  
    SELECT 'Set 1' as MySet, 'TestingMultipleSET1' as txt  
    SELECT 'Set 2' as MySet, 'TestingMultipleSET2' as txt  
GO  
SELECT * FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('TestingMultipleSET'), 0) ;    
SELECT * FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('TestingMultipleSET'), 1) ;    
GO  

----------

If I understand you correctly, then your issue is not specifically with the stored procedure sp_help, but you simply want to know how to get all returned SET from a stored procedure which return multiple SET

OPENROWSET doe not support what you need, So... How do we execute SP which return multiple SETs and use the second SET?

Solution 1: using CLR

The solution is to use SQLCLR. You can build your own CLR stored procedure which get input of the SET that you want to return and use SqlCommand.ExecuteReader Method to retrieve that specific SET - exactly in the same way that sys.dm_exec_describe_first_result_set_for_object works

Solution 2: split the source procedure so each SP will return a single SET

For example if you need the second set returned by sp_help, then you could look at the code behind the scenes of sp_help and base on it, but your stored procedure which only return the second set - build your version for sp_help

By the way, I have such solution using SQLCLR Stored procedure which I developed long time ago. It get name of SP to execute and the SET which you want to return. It uses SqlCommand.ExecuteReader and SqlDataReader.NextResult() to get the right result SET and return it to the client.

I wish Microsoft will provide such solution as build-in! I created a feedback call long time ago in the old User Voice system, but I am not sure if such exists in the new User Voice system.

Answer 2

If you are dead set on it, you can use my CLR procedure ExecAndInsert, which accepts an SQL batch and which produces one table per result set. You can read about it here: https://www.sommarskog.se/share_data.html#CLR.