T-SQL How to retrieve metadata from SQL proc containing temp tables

Question

I've a T-SQL stored proc running in a Microsoft SQL Server 2019 environment. The proc contains temp tables, dynamic SQL and is 2000+ lines. The proc's output has been verified and tested for completeness and accuracy; re-writing the proc is not an option.

Attempts using sp_describe_first_result_set and OPENROWSET to retrieve metadata have failed due to the use of temp tables and dynamic SQL.

Research links used: and this.

Are there any other approaches to retrieve metadata?

Thoughts?

• Erik Reasonable Rates Darling: Two questions (please edit your question to add details): do you only need metadata about the final output or other steps in the procedure? Do you need this once or on a regular basis?

Answer 1

Just one question: why do you need it on the server???

For the client (ODBC/ADO) it is very easy and reliable to read the metadata of the resultset once that resultset is ready for fetching. There is no need to a pre-analyze stored procedure. Just execute it, and before the very first fetch or right after jumping to the next resultset all meta data for result set is available and you can easily prepare buffers for fetching.

I am really confused on what task you are trying to do with sp_describe_first_result_set? I always thought this procedure is redundant and useless.

• TPV: You asked "Why do you need it on the server?"
• TPV: You asked "Why do you need it on the server?" This app is a boutique financial application subject to multiple governmental regulations. Every new release requires a report reconciliation between the previous and current release to ensure no unintended changes have been made. Executing 2 versions of several hundred reports from client device is chatty. Executing on a server eliminates overhead.
• White Owl: @TPV My question should have been read as: "Why do you need it inside the database server???" The solution to your problem is a client (script or compiled) which is run outside of the DB server (on the same machine as DB Server or on another - does not matter). Just extract the resultset out of DB, put it into storage (even back to DB, to a special table) and analyze the result as much as you like. If you are afraid that executing SP would take longer than running sp_desribe_first_result_set - probably, but the execute-extract approach is actually reliable unlike that SP.
• TPV: Thanks and that's where I landed was a client app. Had to ask the community for input as there are a lot of bright people out there. Thanks for taking the time to engage!

Answer 2

Did you try SET FMTONLY ON (link)?

It basically disables procedural code and returns meta-data based on that.

Below example shows how each execution of the proc returns the same meta-data: two result-sets, as if the IF statement didn't exist:

CREATE OR ALTER PROC p 
@i tinyint
AS
DECLARE @SQL varchar(8000)
IF @i = 1
BEGIN
 SET @SQL = 'SELECT name, object_id FROM sys.tables'
 EXEC(@sql)
END
ELSE
BEGIN
 SET @SQL = 'SELECT name FROM sys.tables'
 EXEC(@sql)
END
GO
SET FMTONLY ON
EXEC p @i = 1
EXEC p @i = 2
EXEC p @i = 3
SET FMTONLY OFF

Answer 3

Are there any other approaches to retrieve metadata?

You can using object_definition and for xml path find out about the code of the procedure you are trying to extract data from, see what table it returns and then use with result sets

USE [msdb];
SELECT OBJECT_DEFINITION(OBJECT_ID(N'msdb.dbo.sp_help_job')) FOR XML PATH('');

or you can use the BOL and find out about the procedure you are dealing with (example here would be sp_help_job

Here some working examples:

select *
from openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;',
'exec dbo.sp_who') AS a
SELECT * 
INTO #JobInfo
FROM OPENROWSET('SQLNCLI','Server=(local);Trusted_Connection=yes;'
, 'set fmtonly off;
DECLARE 
 @job_id UNIQUEIDENTIFIER = NULL, 
 @job_name SYSNAME = NULL, 
 @job_aspect VARCHAR(9) = NULL, 
 @job_type VARCHAR(12) = NULL,
 @owner_login_name SYSNAME = NULL,
 @subsystem NVARCHAR(40) = NULL,
 @category_name SYSNAME = NULL,
 @enabled TINYINT = NULL,
 @execution_status INT = NULL,
 @date_comparator CHAR(1) = NULL,
 @date_created DATETIME = NULL,
 @date_last_modified DATETIME = NULL,
 @description NVARCHAR(512) = NULL
 EXEC msdb.dbo.sp_help_job 
 @job_id, 
 @job_name,
 @job_aspect,
 @job_type,
 @owner_login_name,
 @subsystem,
 @category_name,
 @enabled,
 @execution_status,
 @date_comparator,
 @date_created,
 @date_last_modified,
 @description
 WITH RESULT SETS
 ( 
 (
 job_id UNIQUEIDENTIFIER, 
 originating_server NVARCHAR(30), 
 name SYSNAME, 
 [enabled] TINYINT, 
 [description] NVARCHAR(512), 
 start_step_id INT, 
 category SYSNAME, 
 [owner] SYSNAME, 
 notify_level_eventlog INT, 
 notify_level_email INT, 
 notify_level_netsend INT, 
 notify_level_page INT, 
 notify_email_operator SYSNAME, 
 notify_netsend_operator SYSNAME, 
 notify_page_operator SYSNAME, 
 delete_level INT, 
 date_created DATETIME, 
 date_modified DATETIME, 
 version_number INT, 
 last_run_date INT, 
 last_run_time INT, 
 last_run_outcome INT, 
 next_run_date INT, 
 next_run_time INT, 
 next_run_schedule_id INT, 
 current_execution_status INT, 
 current_execution_step SYSNAME, 
 current_retry_attempt INT, 
 has_step INT, 
 has_schedule INT, 
 has_target INT, 
 [type] INT 
 )
 )
') AS A
select * from #JobInfo

Answer 4

I too have a similar issue; I need to be able to interrogate the output of a query/procedure that I don't own (customer provided); that output is then aggregated and charted. Ultimately, I need to take the output of someone else's procedure, drop it in my own table, and then manipulate the data from there. This is actually easy to do, unless, the procedure uses temp tables.

Tibor, if I take your answer, tweak it to match the #TempTable is a special case issue, then the problem is visible. Neither FMTONLY nor sp_describe_first_result_set will survive if the procedure includes a temp table.

ex:

CREATE OR ALTER PROC p 
@i tinyint
AS
SELECT object_id, name
 into #TempTable 
 FROM sys.objects 
 where type = 'S';
IF @i = 1
BEGIN
 SELECT top 10 * from #TempTable
END
ELSE
BEGIN
 SELECT * from #TempTable 
END
GO
--lets examine the meta data... 
exec sp_describe_first_result_set N'exec p @i = 1',null,0
SET FMTONLY ON
EXEC p @i = 1
SET FMTONLY ON

Now, If I take that example and expand it to the "take the output of your procedure and dump that into my table", you can see where the temp table issue takes things sideways:

declare @Cmd varchar(1000),
 @ExecCmd varchar(100);
if object_id(N'..JustForFun') is not null
drop table JustForFun;
set @ExecCmd = 'EXEC master.dbo.sp_who';
set @Cmd = 'SELECT * INTO JustForFun FROM OPENROWSET(''SQLNCLI'',''SERVER=' + @@SERVERNAME + ';Trusted_Connection=yes;'','
 + '''' + @ExecCmd + ''')'
exec(@Cmd);
select * from JustForFun
go
--Now lets try that with the procedure with a temp table... 
declare @Cmd varchar(1000),
 @ExecCmd varchar(100);
if object_id(N'..JustForFun') is not null
drop table JustForFun;
set @ExecCmd = 'Exec ' + db_name() + '.dbo.p @i = 1'
set @Cmd = 'SELECT * INTO JustForFun FROM OPENROWSET(''SQLNCLI'',''SERVER=' + @@SERVERNAME + ';Trusted_Connection=yes;'','
 + '''' + @ExecCmd + ''')'
exec(@Cmd);

The sp_who call works as expected. The call to procedure p with a temp table: Meta data cannot be determined

The only workaround I've found is as White Owl suggested; exec the procedure in code, grab the schema def from the ADO class objects, explicitly create a temp table, then insert the results of the procedure (executed again) into that table with an insert into tabname exec(@Cmd);

In short, there appears to be no way to redirect the output of a procedure,if that procedure uses a temp table, into a table like you can with conventional INSERT INTO ... syntax. You also cannot interrogate the meta data with any of the mentioned schema tools.