How to determine data types after executing a stored procedure?

Question

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

Answer 1

You can look at types, though, if you call a stored procedure through ADO, ADO.NET, ODBC or the like: the resulting record sets have the type information you are looking for. Are you really limited to Management Studio?

Answer 2

It would be best to change the stored procedure to a function. But this only works if your environment allows it.

Answer 3

, , . SP XML, XML_INFO , .

Answer 4

, 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')

, , .

Answer 5

, OPENROWSET proc , sp_help, .

,

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

, garrenteed. kludge, . , .

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