0

Does anyone know if there´s any alternative to the function sys.dm_exec_describe_first_result_set_for_object to retrieve a list of columns from a stored procedure in SQL Server 2008.

The function sys.dm_exec_describe_first_result_set_for_object only works after SQL Server 2012, but I need to complaint with SQL Server 2008.

I already studied the dependencies schema, but it does not bring exactly the same columns because dependencies are linked with the tables. It didn't solve my question.

OPENQUERY is not an alternative in my production environment as well (for security reasons).

mmathias
  • 3
  • 2
  • you can try to use set fmtonly on before stored procedure execution to get resultset metadata – Dmitry Kolchev Apr 22 '20 at 21:36
  • Does this answer your question? [Retrieve column definition for stored procedure result set](https://stackoverflow.com/questions/7368864/retrieve-column-definition-for-stored-procedure-result-set) –  Apr 22 '20 at 21:41

1 Answers1

1

The older method is FMTONLY processing.

eg

set fmtonly on
exec SomeProc
set fmtonly off

Which will return an empty resultset for each static SQL query that returns results in the stored procedure. And does not actually perform any data access or run any DML statements.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Maybe add your answer to [this one](https://stackoverflow.com/questions/7368864/retrieve-column-definition-for-stored-procedure-result-set) then close this as a duplicate? – Dale K Apr 22 '20 at 22:17
  • 1
    I initially searched for a question to close this as a dupe. I looked at that one, but there's a lot of extraneous information about the quirks of working with temporary tables in that one. And being an old question, it's not quite the same as this one, which is asking for an alternative to dm_exec_describe_first_result_set_for_object . Once closed as a dupe, this question would not be searchable. – David Browne - Microsoft Apr 22 '20 at 22:19
  • 1
    I agree, I saw the question mentioned here, and it talks about define the structure of a temp table based in a procedure in SQL 2008. But the alternative given is related with OPENQUERY, which I cannot use in my production, I should have mentioned it. Thanks for the answer, I am using FMTONLY, although I am feeling that it is slower than running the procedure (I am still checking it). But anyway, it is the best alternative I have at this moment. Thank you so much. – mmathias Apr 24 '20 at 07:20