sqlfind

Find information about all table types in MySQL database

collapse all in page

Syntax

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

Description

example

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.

example

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

Examples

collapse all

Open Live Script

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)
Open Live Script

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

collapse all

MySQL native interface database connection, specified as a connection object.

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

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

collapse all

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.

VariableDescription
CatalogCatalog name where the database table type is stored
SchemaSchema name where the database table type is stored
TableDatabase table name
ColumnsColumn names in the database table type
TypeDatabase table type

More About

collapse all

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

See Also

| | |

Topics

Introduced in R2020b

Database Toolbox Documentation

Support