Query to know Data Type of the field of MS SQL Server

want to know query to know data type of various fields in tables

[Ecstacy]

Hi Everyone,

I want to know a query wchich can retrieve the table structure of a particular database including the data type of the table's fields.

Does anyone know such a query? If yes, then please tell me.

- Ecstacy

[WebJoe]

I know in Oracle

SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME= 'YOUR_TABLE'

works, but had to learn that some other DBMS' don't have a table like that.
So what I di is the following:

Dim lconYourDB As ADODB.Connection
Dim lrsTemp As ADODB.Recordset
Dim lfColumn As ADODB.Field
Dim lsConnect As String
Dim liIterator As Integer
Dim lsColName() As String
Dim liColType() As Integer

liIterator = -1
' lsConnect is your connection string
lconYourDB.Open lsConnect
lsSQL = "SELECT * FROM YOUR_TABLE"
lrsTemp.Open lsSQL, lconYourDB, adOpenStatic, adLockReadOnly, adCmdText
For Each lfColumn In lrsTemp.Fields
liIterator = liIterator + 1
ReDim Preserve lsColName(liIterator)
ReDim Preserve liColType(liIterator)
lsColName(liIterator) = lfColumn.Name
liColType(liIterator) = lfColumn.Type
Next
lrsTemp.Close
lconYourDB.Close

Now you have two arrays, lsColName with all the field names and lsColType with the type of data.

[aspdaddy]

Try the ADOX.Catalog object - I wrote an inspector for access, not tried it with sql server.

objCatalog.activeConnection = objConn 
for each item in objCatalog.tables 
 if item.type="TABLE" then 
 ' item.Name item.Type, item.DefinedSize etc
for each key in item.keys
if key.Type=1 then
 ' key.columns(0).Name ...

HTH