List of all columns in FGDB using SQL query

[Bud]

It would be great if we could use an SQL query, such as a database view, to get a list of all fields in all tables in a file geodatabase, including the field properties.

That would help with data management requirements like Identify Feature Classes with 64-bit ObjectID.

For reference, this can be done in enterprise geodatabases by querying the geodatabase system tables. Or by querying a native database table/view such as Oracle's USER_TAB_COLUMNS.

[SSWoodward]

For full SQL functionality, maintaining data in an enterprise geodatabase is advised. 

In File Geodatabase the easiest way to achieve this is with a Schema Report. The 'Fields' mega-sheet will give you information about every field in the geodatabase.

1. Generate a schema report of your geodatabase.

2.  Navigate to the Fields mega-sheet which contains the property information for all fields in the geodatabase

3. Sort the table by field type to group all Object ID fields together.

4. Take note of whether the OID field length is 4 ( standard OID ) or 8 ( 64 bit OID )

Describing the Workspace and then drilling into the fields will also allow you to build something like this without a ton of code.  Building a complete list of all field objects and their properties can be done like below. 

fields_list = list()

for workspace in arcpy.da.Walk(geodatabase):
    desc = arcpy.Describe(workspace[0])

    for child in desc.children:
       if hasattr(child, 'fields'):
            fields_list.extend(child.fields)

Describe Documentation