SQL Server 2005 Determine Stored Procedure Output Type

I'm working on a legacy system, and I need to call a stored procedure to retrieve the data I need. The problem is, I don't have any idea as to what the output column format is. Short of going into the stored procedure开发者_如何学运维 and figuring out the output column format from the SQL, is there a way for me to see what the output column types are? I can run the stored procedure just fine, but the code is a mess, and I'd prefer to treat it as a black box if I could.

EDIT: I know that its not possible for me to determine this from the database metadata, since the procedure may return different results based upon what the input is. I guess I should rephrase my question: given the result set from a stored procedure, how can I determine the column types?

As you already know, you cannot determine that information from any database metadata (since there is none) - and unfortunately, you cannot determine that from the result set, either - at least not in any reliable, deterministic way.

When you call a stored procedure, all you get back is a bunch of columns and a bunch of rows. There's no inherent information available about the types of those columns. Best you can do is guess - if the data contains alphanumeric characters, it's a VARCHAR/string field. If it has only numeric digits, and possibly a decimal separator, it's likely to be a INT or DECIMAL (or MONEY or SMALLMONEY - can't really tell for sure). If it looks like a DATE and can be converted to a DATE, it's probably a DATE, DATETIME, DATETIME2 or something like that.

The only reliable way is to have some documentation on the output values that the stored procedure generates. Anything else is guesswork at best.

what will you do if the stored proc outputs different resultsets depending on what is passed in...for example

create procedure Test
@var int
as
if @var =1
begin
select col1,col2 from table1
end
else if @var =2
begin
select col4,col2 ,col5,col1 from table2
end
else 
begin
select * from table3
end

There is a SET options but it is being deprecated

SET FMTONLY ON;
GO
exec YourProc
GO
SET FMTONLY OFF;
GO