How to tell the data types after executing a stored procedure?

Question

Is there a way when executing a stored procedure in Management Studio to get the data types of the result sets coming back? I'm looking for something like functionality of when you pass a table name to sp_help

Answer 1

No easy way comes to mind without parsing syscomments to see what it's querying from where. If you can edit the SP to select XML, you can append XML_INFO to the query to get the schema back.

Answer 2

You do get to look at the types though, if you call the stored procedure via ADO, ADO.NET, ODBC or the likes: The resulting recordsets have the type information you are looking for. Are you really restricted to Management Studio?

Answer 3

You could always use an actual table that is garrenteed to be unique. It's a kludge, but it's an option. This will not work inside a stored proc though.

if exists (select * from sys.tables where name = 'tmp_TableName')
    drop table tmp_TableName
go
select * into tmp_TableName from MyTable

--do some stuff

go
if exists (select * from sys.tables where name = 'tmp_TableName')
    drop table tmp_TableName
go

Answer 4

Actually, you can do it from within an SP:

EXEC ('if exists (select * from sys.tables where name = ''tmp_TableName'') drop table tmp_TableName')

EXEC ('select * into tmp_TableName from MyTable')

-- Grab the column types from INFORMATION_SCHEMA here

EXEC ('if exists (select * from sys.tables where name = ''tmp_TableName'') drop table tmp_TableName')

Although, I think there must be a better way.

Answer 5

It's not the most elegant solution, but you could use OPENROWSET to put the stored proc results into a table, then use sp_help to get a description of it.

eg

select * into tmp_Results 
from openrowset( 'SQLOLEDB.1'
               , 'Server=your_server_name;Trusted_Connection=yes;'
               , 'exec your_stored_proc')
exec sp_help 'tmp_Results'
drop table tmp_Results

Answer 6

Your best bet would be to change the stored procedure to a function. But that only works if your environment allows it.