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: The metadata could not be determined because statement invokes an extended stored procedure https://stackoverflow.com/questions/58553455/sql-server-the-metadata-could-not-be-determined-because-statement-execute-mas

Are there any other approaches to retrieve metadata?

Thoughts?

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.

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

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:

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.