MS SQL: Get returning data type info of a query

For years I’ve wondered if there’s a way to get the returning data types of a complex query or a stored procedure in Microsoft SQL Server. Something like:

sp_get_return_type_info select firstname, lastname from [user]

OR

sp_get_return_types StoreProcNameHere

I’ve done quick google searches for this many times in the past and this time I really dug deep. I mean like 3 hours deep just on this. And sadly, I could not find anything like that!!

But I did get something good out of this and I don’t want to forget it. I wondered how Visual Studio figures out the returning data types of complex queries that are added to a DataSet. So I ran the SQL Server Profiler as I was adding a new TableAdapter and I saw that they are also not using any magic sp_* call. They use this query to get the datatypes of each of the joined tables:

select col.name,  st.name as DT_name,   
  case when (st.name in ('nchar', 'nvarchar') and (col.max_length > 0))  
    then col.max_length / 2 else col.max_length end as max_length, 
  col.precision,  
  col.scale,  bt.name as BT_name,  col.is_nullable,  col.is_identity,
  col.is_rowguidcol,  
  OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst,   
  CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end) 
    as is_computed,  
  case when(cmc.column_id is null) then null else cmc.definition end 
    as formular,  
  col.collation_name,  col.system_type_id 
from campusvue_train.sys.all_columns col  
left outer join campusvue_train.sys.types st 
  on st.user_type_id = col.user_type_id 
left outer join campusvue_train.sys.types bt 
  on bt.user_type_id = col.system_type_id  
left outer join campusvue_train.sys.identity_columns idc 
  on idc.object_id = col.object_id and idc.column_id = col.column_id  
left outer join campusvue_train.sys.computed_columns cmc 
  on cmc.object_id = col.object_id and cmc.column_id = col.column_id  
where col.object_id = object_id('TABLE_NAME')  order by col.column_id

It’s just a nice join to have handy.

And VS is not able to figure out the datatypes of a table returned by a stored procedure. I tested this on a stored procedure that returned a select on a table variable as opposed to returning a select that came entirely from a normal database table; maybe it supports that, but I doubt it.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s