Determine the columns of a dynamic stored procedure. SQL Server

Question

I have the problem that I need to know the columns of any stored procedure that passes as a parameter inside another. But in any case I do not get any results.

ALTER PROCEDURE getColumnsTable
    (@tableColumnsNames  nvarchar(45))
AS 
BEGIN 
    DECLARE @TSQL varchar(100)

    SET @TSQL = 'select * into #TablaTemporal FROM OPENQUERY( MyServerConnection,  ''EXEC '+   @tableColumnsNames    +''');' 

    EXEC (@TSQL)

    SELECT COLUMN_NAME 
    FROM tempdb.INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME like '%#TablaTemporal%'

    IF EXISTS (SELECT * FROM tempdb.INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME like  '%#TablaTemporal%' )
    BEGIN
        DROP TABLE #TablaTemporal
    END 
END

This is the stored procedure I would like to read

 ALTER PROCEDURE MyProcTest 
 AS 
 BEGIN 
     SET NOCOUNT ON; 

     SELECT
         c.idCliente, f.idFactura   
     FROM
         Cliente c 
     INNER JOIN
         Factura f ON (c.idCliente = f.idCliente)            
     WHERE
         1 = 2 
 END

I would like to obtain a

COLUMN_NAME
idCliente
idFactura

from a simple

 EXEC getColumnsTable @tableColumnsNames = N'MyProcTest'

Is this possible? I have not found the solution.

Answer 1

SQL Server 2008: cas you try like this:

    USE dbtest
    GO
    IF OBJECT_ID('sp_test') IS NOT NULL DROP PROCEDURE sp_test
    GO
    CREATE PROCEDURE sp_test
    AS
    BEGIN

        SET NOCOUNT ON;
        SELECT 1 AS col1, 'string' AS col2
    END
    GO

    IF OBJECT_ID('tempdb..#tt') IS NOT NULL DROP TABLE #tt
    SELECT  * INTO  #tt FROM OPENROWSET('SQLNCLI', 'server=(local);trusted_connection=yes', 'exec dbtest.dbo.sp_test') WHERE 1!=1
    exec tempdb..sp_columns '#tt'

SQL 2012 and up: you can use DMV sys.dm_exec_describe_first_result_set_for_object

Example:

 SELECT * FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('sp_who'), 0) ;

is_hidden column_ordinal name                                                                                                                             is_nullable system_type_id system_type_name
--------- -------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- -------------- --------------------------------------------------------------------------------------------------------------------------------
0         1              spid                                                                                                                             0           52             smallint
0         2              ecid                                                                                                                             0           52             smallint
0         3              status                                                                                                                           0           239            nchar(30)
0         4              loginame                                                                                                                         1           231            nvarchar(128)
0         5              hostname                                                                                                                         0           239            nchar(128)
0         6              blk                                                                                                                              1           175            char(5)
0         7              dbname                                                                                                                           1           231            nvarchar(128)
0         8              cmd                                                                                                                              0           239            nchar(16)
0         9              request_id                                                                                                                       0           56             int

Answer 2

Finally y Solved the problem, the exec its a Incomplete

DECLARE @sqlCompuesta varchar(MAX)
SET @sqlCompuesta='select c.idCliente,f.idFactura '+
                      'from Cliente c inner join Factura f on (c.idCliente=f.idCliente) '+
                      'where 1=1 '


EXEC (   'SELECT * into #temp1  FROM OpenQuery(MyServerConnection, '''+@sqlCompuesta+'''      )   '  +
         'SELECT * FROM tempdb.INFORMATION_SCHEMA.COLUMNS '+
         'WHERE TABLE_NAME like ''%#temp1%''    '  )