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]
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.