Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.0k views
in Technique[技术] by (71.8m points)

database - Find a table when you know the name of a column?

I have a DB in Access with an incredible amount of tables. Unfortunately, the creator used very non-descriptive names, so it's basically impossible to even guess what a table is for just by looking at its name. I need to urgently find a table that contains certain data, and I am pretty sure that I know the name of some of its columns, or at least words contained within the names of the columns. Basically, what I need is some kind of 'Search by column name in every table in the whole database', that shows all tables that contain certain column names.

Is there any way to accomplish this, before I start going one-by-one like a monkey?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

This procedure will list the table name and column name for any columns whose names contain the text you supply. The results are printed in the Immediate window (go there with Ctrl+g)

Public Sub ListTablesWithColumnNamesContaining(ByVal pText As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set db = CurrentDb
For Each tdf In db.TableDefs
    For Each fld In tdf.Fields
        If InStr(1, fld.Name, pText, vbTextCompare) > 0 Then
            Debug.Print tdf.Name & ":", fld.Name
        End If
    Next fld
Next tdf
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...