What is the reason you want to do it?

Very strange question... Why would you need to determine the datatypes for recordset returned by stored proc? You should know them from stored proc definition.

Howevere, it's interesting question to answer in T-SQL.

You can find out the columns datatypes of recordset returned by stored proc. To do this:

1. Make sure that Ad Hoc Distributed Queries are allowed on SQL Server:

sp_configure 'Show Advanced Options', 1

GO

RECONFIGURE

GO

sp_configure 'Ad Hoc Distributed Queries', 1

GO

RECONFIGURE

GO

2. User SELECT INTO with OPENROWSET:

select c.*

from tempdb.sys.columns c

where c.object_id = (select top 1 t.object_id

from tempdb.sys.tables t

where t.name like '#MyTempTable%')

3. Interogate sys.columns and sys.tables to find out column definitions (please note: multiple tables with a name can exist in tempdb, therefore SELECT TOP 1 is used):

select distinct c.*

from tempdb.sys.columns c

where

join tempdb.sys.tables t on t.object_id = c.object_id

where t.name like '#MyTempTable%'

You can use permanent table to SELCT INTO, in this case your query to get definition will look better, but may create problem for concurrent executions.