Working with Database Metadata

Accessing Metadata

In this example, you use the following Database Toolbox™ functions to access metadata:

  1. Connect to the dbtoolboxdemo data source.

    conn = database('dbtoolboxdemo', '', '')
    conn =
           Instance: 'dbtoolboxdemo'
           UserName: ''
             Driver: []
                URL: []
        Constructor: [1x1...
        com.mathworks.toolbox.database.databaseConnect]
            Message: []
             Handle: [1x1 sun.jdbc.odbc.JdbcOdbcConnection]
            TimeOut: 0
         AutoCommit: 'on'
               Type: 'Database Object'
    

    Alternatively, you can use the native ODBC interface for an ODBC connection. For details, see database.

  2. Use the dmd function to create a database metadata objectdbmeta and return its handle, or identifier:

    dbmeta = dmd(conn)
    
    dbmeta =	DMDHandle:...
    [1x1 sun.jdbc.odbc.JdbcOdbcDatabaseMetaData]
  3. Use the get function to assign database properties data, dbmeta, to the variable v:

    v = get(dbmeta)
    v = 
                  AllProceduresAreCallable: 1
                    AllTablesAreSelectable: 1
     DataDefinitionCausesTransactionCommit: 1
       DataDefinitionIgnoredInTransactions: 0
                DoesMaxRowSizeIncludeBlobs: 0
                                  Catalogs: {4x1 cell}
                           CatalogSeparator: '.'
                               CatalogTerm: 'DATABASE'
                       DatabaseProductName: 'ACCESS'
                    DatabaseProductVersion: '04.00.0000'
               DefaultTransactionIsolation: 2
                        DriverMajorVersion: 2
                        DriverMinorVersion: 1
                                DriverName: [1x31 char]
                             DriverVersion: '2.0001 (04.00.6200)'
                       ExtraNameCharacters: [1x29 char]
                     IdentifierQuoteString: '`'
                          IsCatalogAtStart: 1
                    MaxBinaryLiteralLength: 255
                      MaxCatalogNameLength: 260
                      MaxCharLiteralLength: 255
                       MaxColumnNameLength: 64
                       MaxColumnsInGroupBy: 10
                         MaxColumnsInIndex: 10
                       MaxColumnsInOrderBy: 10
                        MaxColumnsInSelect: 255
                         MaxColumnsInTable: 255
                            MaxConnections: 64
                       MaxCursorNameLength: 64
                            MaxIndexLength: 255
                    MaxProcedureNameLength: 64
                                MaxRowSize: 4052
                       MaxSchemaNameLength: 0
                        MaxStatementLength: 65000
                             MaxStatements: 0
                        MaxTableNameLength: 64
                         MaxTablesInSelect: 16
                         MaxUserNameLength: 0
                          NumericFunctions: [1x73 char]
                             ProcedureTerm: 'QUERY'
                                   Schemas: {}
                                SchemaTerm: ''
                        SearchStringEscape: '\'
                               SQLKeywords: [1x461 char]
                           StringFunctions: [1x91 char]
                StoresLowerCaseIdentifiers: 0
          StoresLowerCaseQuotedIdentifiers: 0
                StoresMixedCaseIdentifiers: 0
          StoresMixedCaseQuotedIdentifiers: 1
                StoresUpperCaseIdentifiers: 0
          StoresUpperCaseQuotedIdentifiers: 0
                           SystemFunctions: ''
                                TableTypes: {13x1 cell}
                         TimeDateFunctions: [1x111 char]
                                  TypeInfo: {16x1 cell}
                                       URL: ...
    										'jdbc:odbc:dbtoolboxdemo'
                                  UserName: 'admin'
                     NullPlusNonNullIsNull: 0
                       NullsAreSortedAtEnd: 0
                     NullsAreSortedAtStart: 0
                        NullsAreSortedHigh: 0
                         NullsAreSortedLow: 1
                     UsesLocalFilePerTable: 0
                            UsesLocalFiles: 1
    
  4. Some information is too long to fit in the display area of the field, so the size of the field data appears instead. The Catalogs element is shown as a 4-by-1 cell array. View the Catalog information.

    v.Catalogs
    
    ans = 
    	'D:\Work\databasetoolboxfiles\tutorial'
    	'D:\Work\databasetoolboxfiles\tutorial_copy'
    
  5. Use the supports function to see what properties this database supports:

    a = supports(dbmeta)
    a =
                              AlterTableWithAddColumn: 1
                             AlterTableWithDropColumn: 1
                                  ANSI92EntryLevelSQL: 1
                                        ANSI92FullSQL: 0
                                ANSI92IntermediateSQL: 0
                           CatalogsInDataManipulation: 1
                           CatalogsInIndexDefinitions: 1
                       CatalogsInPrivilegeDefinitions: 0
                             CatalogsInProcedureCalls: 0
                           CatalogsInTableDefinitions: 1
                                       ColumnAliasing: 1
                                              Convert: 1
                                       CoreSQLGrammar: 0
                                 CorrelatedSubqueries: 1
        DataDefinitionAndDataManipulationTransactions: 1
                     DataManipulationTransactionsOnly: 0
                       DifferentTableCorrelationNames: 0
                                 ExpressionsInOrderBy: 1
                                   ExtendedSQLGrammar: 0
                                       FullOuterJoins: 0
                                              GroupBy: 1
                                  GroupByBeyondSelect: 1
                                     GroupByUnrelated: 0
                         IntegrityEnhancementFacility: 0
                                     LikeEscapeClause: 0
                                    LimitedOuterJoins: 0
                                    MinimumSQLGrammar: 1
                                 MixedCaseIdentifiers: 1
                           MixedCaseQuotedIdentifiers: 0
                                   MultipleResultSets: 0
                                 MultipleTransactions: 1
                                   NonNullableColumns: 0
                              OpenCursorsAcrossCommit: 0
                            OpenCursorsAcrossRollback: 0
                           OpenStatementsAcrossCommit: 1
                         OpenStatementsAcrossRollback: 1
                                     OrderByUnrelated: 0
                                           OuterJoins: 1
                                     PositionedDelete: 0
                                     PositionedUpdate: 0
                            SchemasInDataManipulation: 0
                            SchemasInIndexDefinitions: 0
                        SchemasInPrivilegeDefinitions: 0
                              SchemasInProcedureCalls: 0
                            SchemasInTableDefinitions: 0
                                      SelectForUpdate: 0
                                     StoredProcedures: 1
                              SubqueriesInComparisons: 1
                                   SubqueriesInExists: 1
                                      SubqueriesInIns: 1
                              SubqueriesInQuantifieds: 1
                                TableCorrelationNames: 1
                                         Transactions: 1
                                                Union: 1
                                             UnionAll: 1
    

    A 1 for a given property indicates that the database supports that property; a 0 means that the database does not support the property.

  6. Alternatively, use the tables function to retrieve metadata, such as the names and types of the tables in a catalog in the database. Pass the following arguments to this function:

    • dbmeta, the name of the database metadata object.

    • tutorial, the name of the catalog from which you want to retrieve table names.

      t = tables(dbmeta, 'tutorial')
      t = 
          'MSysAccessObjects'    'SYSTEM TABLE'
          'MSysIMEXColumns'      'SYSTEM TABLE'
          'MSysIMEXSpecs'        'SYSTEM TABLE'
          'MSysObjects'          'SYSTEM TABLE'
          'MSysQueries'          'SYSTEM TABLE'
          'MSysRelationships'    'SYSTEM TABLE'
          'inventoryTable'       'TABLE'       
          'productTable'         'TABLE'       
          'salesVolume'          'TABLE'       
          'suppliers'            'TABLE'       
          'yearlySales'          'TABLE'       
          'display'              'VIEW'        
      
  7. Close the database connection.

    close(conn)
    

Resultset Metadata Objects

Use the resultset function to create resultset objects for cursor object. Then, use the rsmd function to get metadata information about the resultset objects.

For details, see the resultset and rsmd function reference pages.

Was this topic helpful?