Table Column Data_Type - int to English?

Question

Table Column Data_Type - int to English?

toddhd

SSC Veteran

Points: 205
More actions
August 14, 2003 at 7:45 pm

#83044

I am writing a small utility in asp.net to help me code stored procedures. So far, I have a function that lists all the tables in a given database. Selecting a table lists all the columns in the table.
Now here is the problem - the "Data_Type" of each column shows up as an integer value, not something in English, like "char", "int" or "datetime". For exmaple, an "int" value shows up as a data_type of "3", while a "varchar" shows up as a data_type of "130".
Is there a list somewhere off all the data_types and their associated integer value? I've been searching on the net all night, and can't seem to locate it?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

toddhd SSC Veteran Points: 205 More actions · Answer 1

Darn it - just answered my own question. Found the info here, figured I'd share in case someone else was looking for it too:

http://www.able-consulting.com/ADODataTypeEnum1.htm

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 2

Thks, will end up as another A4 poster on the cubicle wall

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Another trick could be to look at adovbs.inc, which also contains those enums

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

toddhd SSC Veteran Points: 205 More actions · Answer 4

Ok, I'm back again. Here is the problem. I wasn't happy with the info I had so far, because if I go into Enterprise Manager and design a table, there are more data type choices listed in the drop down then are listed in the resources. Ok, so let's do it the hard way...

I went into Ent.Man. again, created a table, and made one column for every data type in the dropdown. (bigint, binary, bit, char, etc.). Then I used my program to list the schematable contents of each column. Great! So now all I have to do is map each value in the Data_type column to a dropdown name. Ok, so bigint=20, binary=128, bit=11 - hey wait, an image is listed as 128 also. And for that matter, timestamp and varbinary are listed as =128. What the hey?

Ok, so I'm pretty sure that SQL Server considers them all to be the same thing, and that is fine. But since this program is going to be writing Stored Procs, it would be really nice (and professional) if the data type it returns matches the one I chose.

Problem is - I'm stuck. There must be some other place, some other schema column that positively identifies what kind of data type it is. But looking at all the columns, there is not difference between them (except for the Column_Flags column, which I already learned the hard way isn't going to help me).

Any ideas folks???