Display DataType and Size of Column from SQL Server Query Results at Runtime

Question

Is there a way to run a query and then have SQL Server management studio or sqlcmd or something simply display the datatype and size of each column as it was received.

Seems like this information must be present for the transmission of the data to occur between the server and the client. It would be very helpful to me if it could be displayed.

A little background: The reason I ask is because I must interface with countless legacy stored procedures with anywhere from 50 to 5000+ lines of code each. I do not want to have to try and follow the cryptic logic flow in and out of temp tables, into other procedures, into string concatenated eval statement and so on. I wish to maintain no knowledge of the implementation, simply what to expect when they work. Unfortunately following the logic flow seems to be the only way to figure out what exactly is being returned without trying to infer what the actual types of the data string representations om management studio studio or from the native type in .net for example.

To clarify: I am not asking about how to tell the types of a table or something static like that. I'm pretty sure something like sp_help will not help me. I am asking how to tell what the sql server types (ie varchar(25), int...) are of what I have been given. Additionally, changing the implementation of the sprocs is not possible so please consider that in your solutions. I am really hoping there is a command I have missed somewhere. Much appreciation to all.

Update I guess what I am really asking is how to get the schema of the result set when the result set originates from a query using a temp table. I understand this to be impossible but don't find much sense with that conclusion because the data is being transmitted after all. Here is an example of a stored procedure that would cause a problem.

CREATE PROCEDURE [dbo].[IReturnATempTable]
AS

Create table #TempTable 
( 
    MyMysteryColumn char(50)
)

INSERT #TempTable (
    MyMysteryColumn
) VALUES ( 
    'Do you know me?' ) 


select TOP 50 * FROM #TempTable 

Answer 1

What will you do about stored procedures which return different result sets based on their parameters?

In any case, you can configure a SqlDataAdapter.SelectCommand, along with the necessary parameters, then call the FillSchema method. Assuming that the schema can be determined, you'll get a DataTable configured with correct column names and types, and some constraints.