Get The No. Of Columns!

Arpan · Sep 7, 2005

How do I find out how many columns exist in a SQL Server DB table
before displaying the records of that table?

Also how do I get all the column names as an array so that I can
seperate each column using the Split function?

Thanks,

Arpan

Chris Hohmann · Sep 7, 2005

Arpan said:
How do I find out how many columns exist in a SQL Server DB table
before displaying the records of that table?

Also how do I get all the column names as an array so that I can
seperate each column using the Split function?

Thanks,

Arpan

Use the Information_Schema.Columns system view to get the names of the
columns for a given table:

SELECT column_name
FROM Information_Schema.Columns
WHERE table_name = 'MyTable'

You can then use the GetRows method of the ADODB.Recordset object to
retrieve the column names into a two (2) dimensional array. Here's a link to
the online documentation for the GetRows method:
http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthgetrows.asp

Arpan · Sep 7, 2005

Thanks Chris for your help. It has served my purpose.

Thanks once again.

Regards,

Arpan

Egbert Nierop \(MVP for IIS\) · Sep 7, 2005

Arpan said:
How do I find out how many columns exist in a SQL Server DB table
before displaying the records of that table?

Also how do I get all the column names as an array so that I can
seperate each column using the Split function?

Dim rs As Recordset
Set rs = New Recordset
rs.MaxRecords = 1
rs.Open "yourtable", [yourconn], adOpenForwardOnly
Dim fld As Field
Dim fldCount As Long
lng = rs.Fields.Count
ReDim fldARray(lng - 1) As String

Dim cx As Long
For cx = 0 To lng - 1
fldARray(cx) = rs.Fields.Item(cx).Name
Next

Arpan · Sep 7, 2005

Chris I have another question regarding the GetRows() method. How do I
get the column names using GetRows()? I think that isn't possible,
isn't it?

Thanks once again & thanks to Egbert too

Regards,

Arpan

Chris Hohmann · Sep 7, 2005

Arpan said:
Chris I have another question regarding the GetRows() method. How do I
get the column names using GetRows()? I think that isn't possible,
isn't it?

What I was suggesting was to use the query on the Information_Schema.Columns
view in _conjunction_ with the GetRows method of the ADODB.Recordset object.
The resulting two (2) dimensional array will contain the names of the
columns in the table you specified in the query.

Arpan · Sep 8, 2005

That's exactly what I did but if I am not wrong, the resulting
2-dimensional array will contain the NUMBER of columns in the table (&
not the NAMES of the columns) & the number of records in the table,
isn't it?

Please do correct me if I am wrong.

Thanks once again,

Regards,

Arpan

Evertjan. · Sep 8, 2005

Arpan wrote on 08 sep 2005 in microsoft.public.inetserver.asp.general:

That's exactly what I did but if I am not wrong, the resulting
2-dimensional array will contain the NUMBER of columns in the table (&
not the NAMES of the columns) & the number of records in the table,
isn't it?

Don't know about SQL-server, I use Microsoft.Jet.OLEDB.4.0.

I use this:

<%@Language=VBScript%>


<body style='background-color:navy;padding:50px;'>
<div style='padding:30px;background-color:white;font-
size:14pt;width:350px;'>

<%
dim columnTypes(205)
columnTypes(0) = "Empty"
columnTypes(2) = "SmallInt"
columnTypes(3) = "Integer"
columnTypes(4) = "Real"
columnTypes(5) = "Double"
columnTypes(6) = "Currency"
columnTypes(7) = "Date"
columnTypes(8) = "BSTR"
columnTypes(9) = "IDispatch"
columnTypes(10) = "Error Code"
columnTypes(11) = "Boolean"
columnTypes(12) = "Variant"
columnTypes(13) = "IUnknown"
columnTypes(14) = "Decimal"
columnTypes(16) = "TinyInt"
columnTypes(17) = "Unsigned TinyInt (BYTE)"
columnTypes(18) = "Unsigned Small Int (WORD)"
columnTypes(19) = "Unsigned Int (DWORD)"
columnTypes(20) = "BigInt"
columnTypes(21) = "Unsigned Big Int"
columnTypes(64) = "FileTime"
columnTypes(72) = "Unique Identifier (GUID)"
columnTypes(128) = "Binary"
columnTypes(129) = "Char"
columnTypes(130) = "nChar"
columnTypes(131) = "Numeric"
columnTypes(132) = "User Defined (UDT)"
columnTypes(133) = "DBDate"
columnTypes(134) = "DBTime"
columnTypes(135) = "SmallDateTime"
columnTypes(136) = "Chapter"
columnTypes(138) = "Automation (PropVariant)"
columnTypes(139) = "VarNumeric"
columnTypes(200) = "VarChar"
columnTypes(201) = "Text"
columnTypes(202) = "nVarChar"
columnTypes(203) = "nText"
columnTypes(204) = "VarBinary"
columnTypes(205) = "Image"

function tables(tablename)
Response.Write "<hr><hr>"&tablename&"<hr>"
set rs = CONNECT.Execute("select * from " & tablename)
for each column in rs.fields
Response.Write column.name & " [" & columnTypes(column.type)
ct = column.type
if ct = 129 or ct = 130 or ct = 200 or ct = 202 then
' definedSize only works in SQL Server
Response.write " (" & column.definedSize & ")"
end if
Response.Write "]"
if column.Properties("IsAutoIncrement") then
response.write " (AutoNumber)"
end if
Response.Write "<br>"
next
rs.close: set rs = nothing
end function

tables("tblNumber1") ' name your tables

tables("tblNumber2")

' etc.

CONNECT.close: set CONNECT = nothing
%>
</div>

I forgot where the souce was from.

Chris Hohmann · Sep 8, 2005

Arpan said:
That's exactly what I did but if I am not wrong, the resulting
2-dimensional array will contain the NUMBER of columns in the table (&
not the NAMES of the columns) & the number of records in the table,
isn't it?

Please do correct me if I am wrong.

It's very difficult to diagnose your problem since I don't have any idea
what your code looks like. Please post your code, the output of that code
and the desired results.

How can I find occurrences of a column name FPPaymentID in the entire database (e.g table, stored procedure etc) in SSMS?	2	Jun 20, 2023
How do i get numberOfItemsHired to only accept 1-500 if it is outside those values error message should be displayed	10	Jul 5, 2024
Get Columns From SP!	0	Sep 29, 2005
Array Get Value	7	Sep 4, 2023
Columns	5	Jun 22, 2012
Decoding no of ways and printing each decode message	2	Jun 1, 2021
How to get education and coding job coming from abroad starting new in the US? Advice of courses or places to look?	2	May 18, 2023
Select DB Columns Dynamically!	7	Aug 31, 2005

Get The No. Of Columns!

Arpan

Chris Hohmann

Arpan

Egbert Nierop \(MVP for IIS\)

Arpan

Chris Hohmann

Arpan

Evertjan.

Chris Hohmann

Ask a Question

Similar Threads

Members online

Forum statistics

Latest Threads