SQL Server 2014 - sp_describe_cursor_column for dynamic queries issue

Question

Basically I want to know columns/aliases of result set from dynamic query. I tried to use sp_describe_cursor_column but without success.

It returns me that cursor does not exits. But I can fetch values from such cursor...

The code is :

ALTER PROC TestProc
AS
       DECLARE @dynamicSQL nvarchar(200)
       -- Have code that will construct the dynamic SQL
   SET @dynamicSQL = '  select table_name, TABLE_TYPE from INFORMATION_SCHEMA.TABLES'

   -- The cursor that will be filled by the dynamic SQL
   DECLARE @outputCursor CURSOR
   -- Create the dynamic SQL to fill a CURSOR instead
   SET @dynamicSQL = 'SET @outputCursor = CURSOR FORWARD_ONLY STATIC FOR ' +
          @dynamicSQL + ' ; OPEN @outputCursor'

   -- Execute dynamic sql
   exec sp_executesql                  -- sp_executesql will essentially create a sproc
          @dynamicSQL,                 -- The SQL statement to execute (body of sproc)
          N'@outputCursor CURSOR OUTPUT', -- The parameter list for the sproc: OUTPUT CURSOR
          @outputCursor OUTPUT         -- The parameter to pass to the sproc: the CURSOR


declare @Report cursor 

 exEC sp_describe_cursor_columns
    @cursor_return = @Report OUTPUT
    ,@cursor_source = N'local' 
    ,@cursor_identity = N'outputCursor';


   -- Code that will just output the values from the cursor
   DECLARE @tableName nvarchar(200), @table_type nvarchar(200);
   FETCH NEXT FROM @outputCursor INTO @tableName, @table_type

   -- Loop while there're more things in the cursor
   WHILE @@FETCH_STATUS = 0
   BEGIN
          PRINT @tableName
          FETCH NEXT FROM @outputCursor INTO @tableName, @table_type
   END

   -- Be nice, close & deallocate cursor
   CLOSE @outputCursor
   DEALLOCATE @outputCursor

And this is the result:

Msg 16916, Level 16, State 4, Procedure sp_describe_cursor_columns, Line 23 A cursor with the name 'outputCursor' does not exist. DATABASE_UPDATE SYSTEM_CONFIGURATION ....

I want as result to see table_name , table_type. Don't tell me that I can just extarct it from string, becasue user may send select * from xxxx.

Answer 1

I found some other way how to extract description of result set for dynamic queries.

declare @Table nvarchar(200) ;
DECLARE @dynamicSQL nvarchar(200)
SET @dynamicSQL = 'select table_name, TABLE_TYPE from INFORMATION_SCHEMA.TABLES'


SELECT  @Table = COALESCE(@Table + ', ', '') + Name
FROM sys.dm_exec_describe_first_result_set
        (@dynamicSQL, NULL,1)

print @Table