Get Stored Procedure Result Column Types
You should be able to use the new system stored procedure sp_describe_first_result_set
for that - see the MSDN docs for details:
EXEC sp_describe_first_result_set N'YourStoredProcedureNameHere'
Specifically for objects, there is a DMV called sys.dm_exec_describe_first_result_set_for_object which will describe the first result set if SQL Server can figure out what it should be (dynamic SQL, for example won't return a valid result).
Specifically for T-SQL or batch related items there is a different DMV and accompanied system stored procedure. The DMV is sys.dm_exec_describe_first_result_set and the stored procedure that parallels the dmv is sp_describe_first_result_set.
If you use Openquery to insert the results of the stored procedure into a temp table, you can query the columns for the temp table.
Note, you cannot use a @variable in Openquery, so you will need to run it through dynamic sql and an exec. The problem with that is now the temp table doesn't exist outside the dynamic query. But, if you put your temp table into a global query you can still access it. Then just query the system tables for the column info as you normally would.
Create PROCEDURE TheProcedure
AS
BEGIN
select 1 id,
'textstring' textstring,
convert(bit, 'true') boolean
END
GO
drop table ##test
declare @query varchar(255)
select @query = 'select * '
select @query = @query + 'into ##test '
select @query = @query + 'from Openquery([' + @@SERVERNAME + '], ''exec misdb.dbo.TheProcedure'') '
EXEC(@query)
select AC.name columnName,
T.name dataType
from tempdb.Sys.columns AC
join tempdb.sys.types T
on AC.system_type_id = T.system_type_id
where object_id = object_id('tempdb.dbo.##test')
order by AC.column_id
/*
columnName dataType
---------- --------
id int
textstring varchar
boolean bit
*/