sqlfind

Retrieve metadata about database objects

Syntax

data = sqlfind(conn,pattern)

data = sqlfind(conn,pattern,Name=Value)

Description

data = sqlfind(conn,pattern) returns metadata table for database objects such as tables and views, whose names match the specified pattern.

conn can be any of the following connection objects:

• MySQL^®

• PostgreSQL^®

• DuckDB™

• ODBC

• JDBC

Use this function to discover table names and validate schema structure before running queries.

data = sqlfind(conn,pattern,Name=Value) applies additional options specified by one or more name-value arguments. For example, Catalog="cat" searches all table types within the specified catalog.

Examples

Find Information About Table Types Using PostgreSQL Native Interface

Use a PostgreSQL native interface database connection to find information about all database table types in a PostgreSQL database.

Create a PostgreSQL native interface database connection to a PostgreSQL database.

datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";

conn = postgresql(datasource,username,password);

Find information about all table types in the database.

data = sqlfind(conn,"");

Display information about the first three table types.

head(data,3)

ans=3×5 table
    "toystore_doc"    "information_schema"    "_pg_foreign_data_wrappers"    1×7 string    "VIEW"
    "toystore_doc"    "information_schema"          "_pg_foreign_servers"    1×9 string    "VIEW"
    "toystore_doc"    "information_schema"    "_pg_foreign_table_columns"    1×4 string    "VIEW"

data contains these variables:

• Catalog name

• Schema name

• Table name

• Columns in the table type

• Table type

Close the database connection.

close(conn)

Find Information About Table in DuckDB Database

Load airline data.

airlineData = readtable("airlinesmall_subset.xlsx");

Create a connection to a DuckDB™ database file by using the duckdb function. Specify a name for the database file where you store the airline data.

conn = duckdb("airlineDatabase.db");

Export the airline data to a database table, "airlinesTable", by using the sqlwrite function.

tablename = "airlinesTable";
sqlwrite(conn,tablename,airlineData)

Use the sqlfind function to search for DuckDB™ tables that have "airlines" in the table name. Verify that sqlfind finds the table, "airlinesTable", that you created.

sqlfind(conn,"airlines")

ans=1×5 table
         Catalog         Schema         Table                                                                                                                                                                                                                                   Columns                                                                                                                                                                                                                                  Type    
    _________________    ______    _______________    ___________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________    ____________

    "airlineDatabase"    "main"    "airlinesTable"    {["Year"    "Month"    "DayofMonth"    "DayOfWeek"    "DepTime"    "CRSDepTime"    "ArrTime"    "CRSArrTime"    "UniqueCarrier"    "FlightNum"    "TailNum"    "ActualElapsedTime"    "CRSElapsedTime"    "AirTime"    "ArrDelay"    "DepDelay"    "Origin"    "Dest"    "Distance"    "TaxiIn"    "TaxiOut"    "Cancelled"    "CancellationCode"    "Diverted"    "CarrierDelay"    "WeatherDelay"    "SDelay"    "SecurityDelay"    "LateAircraftDelay"]}    "BASE TABLE"

Find Information About Table Types in Catalog and Schema

Use an ODBC connection to find information about all database table types in a Microsoft® SQL Server® database. Specify the database catalog and schema to search.

Create an ODBC database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

Check the database connection. If the Message property is empty, then the connection is successful.

conn.Message

ans =

     []

Find information about all table types in the toy_store database catalog and the dbo database schema. Use the 'Catalog' name-value pair argument to specify the catalog. Use the 'Schema' name-value pair argument to specify the schema.

data is a table that contains information about all the table types in the specified catalog and schema.

data = sqlfind(conn,'','Catalog','toy_store','Schema','dbo');

Display the first eight table types.

head(data)

ans =

  8×5 table

      Catalog      Schema          Table            Columns       Type  
    ___________    ______    __________________    __________    _______

    'toy_store'    'dbo'     'DS17111713025590'    {1×5 cell}    'TABLE'
    'toy_store'    'dbo'     'DS17111713025699'    {1×4 cell}    'TABLE'
    'toy_store'    'dbo'     'DS22121715025751'    {1×5 cell}    'TABLE'
    'toy_store'    'dbo'     'DS22121715025879'    {1×4 cell}    'TABLE'
    'toy_store'    'dbo'     'DS22121715052820'    {1×5 cell}    'TABLE'
    'toy_store'    'dbo'     'DS22121715052941'    {1×4 cell}    'TABLE'
    'toy_store'    'dbo'     'DS26121710493780'    {1×5 cell}    'TABLE'
    'toy_store'    'dbo'     'DS26121710493818'    {1×4 cell}    'TABLE'

data contains these variables:

• Catalog name

• Schema name

• Table name

• Columns in the database table

• Table type

Display the column names in the fourth table type.

data.Columns{4}

ans =

  1×4 cell array

    {'productNumber'}    {'Quantity'}    {'Price'}    {'inventoryDate'}

Close the database connection.

close(conn)

Input Arguments

conn — Database connection

MySQLconnection object | PostgreSQL connection object | DuckDB connection object | ODBC connection object | JDBC connection object

Database connection, specified as any of the following:

• MySQL connection object created by using the mysql function.

• PostgreSQL connection object created by using the postgresql function.

• DuckDB connection object created by using the duckdb function.

• ODBC connection object created by using the database function.

• JDBC connection object created by using the database function.

pattern — Search term for database object

string scalar | character vector

Search term for database object, specified as a string scalar or character vector. The sqlfind function looks for this text in the names of database tables and views.

Name-Value Arguments

Specify optional pairs of arguments as Name1=Value1,...,NameN=ValueN, where Name is the argument name and Value is the corresponding value. Name-value arguments must appear after other arguments, but the order of the pairs does not matter.

Before R2021a, use commas to separate each name and value, and enclose Name in quotes.

Example: data = sqlfind(conn,pattern,Catalog="toy_store",Schema="dbo") retrieves metadata for database objects in the toy_store catalog and dbo schema whose names match the specified pattern.

Catalog — Database catalog name

string scalar | character vector

Database catalog name, specified as a string scalar or character vector. A catalog serves as the container for the schemas in a database and contains related metadata information.

Example: Catalog="toy_store"

Schema — Database schema name

string scalar | character vector

Database schema name, specified as a string scalar or character vector. A schema defines the database tables, views, relationships among tables, and other elements.

Example: Schema="dbo"

Note

This argument is not valid when conn is an MySQL connection object.

Output Arguments

data — Metadata about database objects

table

Metadata about database objects, returned as a table containing the variables Catalog, Schema, Table, Columns, and Type. Refer to the following table for a description of each variable. For more information about Type, see Table Types.

Variable	Description	Variable Data Type
Catalog	Catalog name where the database table is stored	Cell array of character vectors
Schema	Schema name where the database table is stored
Table	Database table name
Columns	Column names in the database table
Type	Database table type

More About

Table Types

Table types are a subset of database objects, which store or reference data.

The sqlfind function recognizes these table types in a database:

• Table

• View

• System table

• System view

• Synonym

• Global temporary table

• Local temporary table

Version History

Introduced in R2018a

R2026a: sqlfind supports DuckDB database connections

Retrieve metadata about DuckDB database objects by specifying a DuckDB connection object when using the sqlfind function.