How do I print out the list of Fields and Datatypes in SQL Server 2k
[97 byte] By [Matrix.net] at [2007-11-11 10:15:20] 
Trying to print out the list of fields and their datatypes in sql server 2000 . Anyone know how ?
# 1 Re: How do I print out the list of Fields and Datatypes in SQL Server 2k
Trying to print out the list of fields and their datatypes in sql server 2000 . Anyone know how ?I'm trying to do something similar to this, also, but with MS Access 2k3.
I just need the fieldnames, but I don't want to do SELECT * and return everything.
Anybody?
chupacabra at 2007-11-11 23:43:25 >
# 2 Re: How do I print out the list of Fields and Datatypes in SQL Server 2k
"Print out" in what context? From a program, for documentation purposes, or something else?Phil Weber at 2007-11-11 23:44:25 >
# 3 Re: How do I print out the list of Fields and Datatypes in SQL Server 2k
for me, i just want the names of the fields returned in an ADODB recordset.chupacabra at 2007-11-11 23:45:28 >
# 4 Re: How do I print out the list of Fields and Datatypes in SQL Server 2k
Dim fld as ADODB.FieldFor Each fld In rs.Fields
Debug.Print fld.Name
Next fld
pclement at 2007-11-11 23:46:34 >
# 5 Re: How do I print out the list of Fields and Datatypes in SQL Server 2k
Dim fld as ADODB.FieldFor Each fld In rs.Fields
Debug.Print fld.Name
Next fld
is there a way to get the fields without returning data, with a sql query?
if i say select * then go through they way you're saying, that would involve a lot of data. I just need to know the field names so I can store them for a future query.
chupacabra at 2007-11-11 23:47:33 >
# 6 Re: How do I print out the list of Fields and Datatypes in SQL Server 2k
Trying to print out the list of fields and their datatypes in sql server 2000 . Anyone know how ?The value for DATA_TYPE corresponds to the ADODB.DataTypeEnum.
Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
cnn.Open "Provider=sqloledb;" & _
"Data Source=(local);" & _
"Initial Catalog=Northwind;" & _
"Integrated Security=SSPI"
Set rs = cnn.OpenSchema(adSchemaColumns, Array(Empty, Empty, "Customers", Empty))
While Not rs.EOF
For Each fld In rs.Fields
Debug.Print fld.Name & ": " & fld.Value
Next fld
rs.MoveNext
Wend
cnn.Close
pclement at 2007-11-11 23:48:29 >
# 7 Re: How do I print out the list of Fields and Datatypes in SQL Server 2k
i just need the fieldnames. i'm doing this currently, adapting the above.is there a better way?
Dim fldNames() As String
Set rs = cnn.OpenSchema(adSchemaColumns, Array(Empty, Empty, DataTable, Empty))
With rs
.MoveFirst
Do While Not .EOF
ReDim Preserve fldNames(rCount)
fldNames(rCount) = .Fields(3).Value
DetPrint .Fields(3).Name & ": " & .Fields(3).Value, frmMainForm
rCount = rCount + 1
.MoveNext
Loop
End With
chupacabra at 2007-11-11 23:49:27 >
# 8 Re: How do I print out the list of Fields and Datatypes in SQL Server 2k
Back to my OpenSchema example:While Not rs.EOF
For Each fld In rs.Fields
If fld.Name = "COLUMN_NAME" THEN
Debug.Print fld.Value
Exit For
End If
Next fld
rs.MoveNext
Wend
pclement at 2007-11-11 23:50:35 >
