Take the 2-minute tour ×

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?

share|improve this question
Great question - unfortunately, without any great answer, I'm afraid.... –  marc_s Sep 7 '10 at 20:29

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
I see. I was hoping that there was some metadata that SQL Server Management Studio was leaving out. I guess there isn't, and I'll have to dive into the SQL source of the stored procedure to see what I'm dealing with :( Anyway, thanks for the clear answer. –  quanticle Sep 7 '10 at 21:04

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

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

There is a SET options but it is being deprecated

exec YourProc
share|improve this answer
I know its not possible to determine the result set statically, for exactly this reason. I guess what I want to know is, given a result set from a stored procedure, how can I determine the column types of the result set? I've edited the question clarify this point. –  quanticle Sep 7 '10 at 19:26
Where do you see that SET FMTONLY is being deprecated? It's not indicated in BOL anywhere. –  bluefooted Sep 7 '10 at 20:30

Your Answer


