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?
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.
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
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.
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