Retrieve column data type using OpenSchema

Last Post 08 Mar 2005 11:27 PM by dopoto. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
dopoto
New Member
New Member

--
08 Mar 2005 10: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

--
08 Mar 2005 11:27 PM
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 & "'")
You are not authorized to post a reply.

Acceptable Use Policy