sqlfind

Find information about all table types in MySQL database

Since R2020b

Syntax

data = sqlfind(conn,pattern)

data = sqlfind(conn,pattern,'Catalog',catalog)

Description

data = sqlfind(conn,pattern) returns information about all the Table Types in a database where the specified character pattern appears in the name of the table type. Executing this function is the equivalent of writing the SQL statement SELECT * FROM information_schema.tables.

data = sqlfind(conn,pattern,'Catalog',catalog) finds table types in the specified catalog.

Examples

Find Information About Table Types Using MySQL Native Interface

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

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

datasource = "MySQLNative";
username = "root";
password = "matlab";

conn = mysql(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
    Catalog    Schema        Table            Columns        Type  
    _______    ______    ______________    _____________    _______

    "mysql"      ""      "columns_priv"    {1×7  string}    "TABLE"
    "mysql"      ""      "db"              {1×22 string}    "TABLE"
    "mysql"      ""      "engine_cost"     {1×6  string}    "TABLE"

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 Types in Catalog

Use a MySQL® native interface database connection to find information about all database table types in a MySQL database. Specify the database catalog to search.

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

datasource = "MySQLNative";
username = "root";
password = "matlab";
conn = mysql(datasource,username,password);

Find information about all table types in the mysql database catalog. Use the 'Catalog' name-value pair argument to specify the catalog. data is a table that contains information about all the table types in the specified catalog.

data = sqlfind(conn,"",'Catalog',"mysql");

Display the first eight table types.

head(data)

ans=8×5 table
    Catalog    Schema         Table            Columns        Type  
    _______    ______    _______________    _____________    _______

    "mysql"      ""      "columns_priv"     {1×7  string}    "TABLE"
    "mysql"      ""      "db"               {1×22 string}    "TABLE"
    "mysql"      ""      "engine_cost"      {1×6  string}    "TABLE"
    "mysql"      ""      "event"            {1×22 string}    "TABLE"
    "mysql"      ""      "func"             {1×4  string}    "TABLE"
    "mysql"      ""      "general_log"      {1×6  string}    "TABLE"
    "mysql"      ""      "gtid_executed"    {1×3  string}    "TABLE"
    "mysql"      ""      "help_category"    {1×4  string}    "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×22 string
    "db"    "name"    "body"    "definer"    "execute_at"    "interval_value"    "interval_field"    "created"    "modified"    "last_executed"    "starts"    "ends"    "status"    "on_completion"    "sql_mode"    "comment"    "originator"    "time_zone"    "character_set_client"    "collation_connection"    "db_collation"    "body_utf8"

Close the database connection.

close(conn)

Input Arguments

conn — MySQL^® native interface database connection

connection object

MySQL native interface database connection, specified as a connection object. Starting in R2024a, it is recommended that you use setSecret and getSecret to store and retrieve your credentials for databases that require authentication. For more details, refer to this example.

pattern — Pattern

character vector | string scalar

Pattern, specified as a character vector or string scalar. The sqlfind function searches for this text in the names of the table types in a database. To find all table types, specify an empty character vector or string scalar.

Example: "inventory"

Data Types: char | string

catalog — Database catalog name

character vector | string scalar

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

Example: 'toy_store'

Data Types: char | string

Output Arguments

data — Table type information

table

Table type information, returned as a table that contains information about the table types in the database, where the table type name partially or fully matches the text in pattern. The data output contains these variables in a string array.

Variable	Description
Catalog	Catalog name where the database table type is stored
Schema	Schema name where the database table type is stored
Table	Database table name
Columns	Column names in the database table type
Type	Database table type

More About

Table Types

Table types are a subset of database objects that 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 R2020b