Count of fields in Database

[Suttd via AccessMonster.com]

Hi all,

Hopefully someone will know this one, i need to find out how many fileds
there are within one of the databases I query - i would count but the are
well over a 100 tables and really hoped someone may know a quick way to
retrieve the number of fileds contained within them ? I am using SQL
advantage to run the query and the database is on a sybase platform if that
helps .....

Thanks in advance

Dave

 

[Clifford Bass]

Hi Dave,

Usually you can just query the appropriate system table that contains
the column metadata. If that is not available in SyBase you could try
something like this:

Public Sub GetTableAndColumnCounts()

Dim cnnDatabase As New ADODB.Connection
Dim rstColumnsSchema As ADODB.Recordset
Dim rstTablesSchema As ADODB.Recordset
Dim lngColumnCount As Long
Dim lngTableCount As Long
Dim strTableName As String
Dim strTableType As String

lngColumnCount = 0
lngTableCount = 0
' Use appropriate OLE or ODBC connection string for connecting to SyBase
cnnDatabase.Open "Connection string to your SyBase database"
Set rstTablesSchema = cnnDatabase.OpenSchema(adSchemaTables)
With rstTablesSchema
Do While Not .EOF
strTableType = rstTablesSchema.Fields("TABLE_TYPE").Value
If strTableType <> "SYSTEM TABLE" And strTableType <> "VIEW" Then
lngTableCount = lngTableCount + 1
strTableName = rstTablesSchema.Fields("TABLE_NAME").Value
Set rstColumnsSchema =
cnnDatabase.OpenSchema(adSchemaColumns, _
Array(Empty, Empty, strTableName, Empty))
With rstColumnsSchema
Do While Not .EOF
lngColumnCount = lngColumnCount + 1
.MoveNext
Loop
.Close
End With
Set rstColumnsSchema = Nothing
End If
.MoveNext
Loop
.Close
End With
Set rstTablesSchema = Nothing
cnnDatabase.Close
Set cnnDatabase = Nothing

MsgBox "Tables: " & lngTableCount & vbCrLf & "Columns: " & lngColumnCount

End Sub

You may need to refine it to deal with how SyBase reports schema
information. Also, instead of just counting, you could put the information
into a local table where you could view it to see if it is collecting for the
correct tables.

Hope that helps,

Clifford Bass