Retrieve column data type using OpenSchema

Last Post 09 Mar 2005 12:27 AM by dopoto. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
dopoto
New Member
New Member

--
08 Mar 2005 11:49 PM
Hi,
I am trying to retrieve info about the structure of a MS-SQL Server table using the OpenSchema method. For example, having the table Cars, I want the follwing info returned:

Column DataType Length
------- -------- ------
ID int
Name varchar 50
...

I have used "Set RS = Con.OpenSchema(adSchemaColumns)", but this doesn't seem to offer enough info for the DataType column above - what I need it to return datatypes as you'd see in the Design Mode of the table (int, varchar, ...)

Can you guys offer me some help on this?
dopoto
New Member
New Member

--
09 Mar 2005 12:27 AM
Got it! For posterity's sake and for other confused users who might pass by, here it is:

strTabl = the table you want information on
strDBname = the database name
Set objColumnRS = objConn.Execute( _
"SELECT COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH, " & _
"NUMERIC_PRECISION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME
= '" & strTbl & _
"' AND TABLE_CATALOG = '" & strDBname & "'")


Acceptable Use Policy
---