sqlfind

Find information about all table types in PostgreSQL database

Since R2020b

Syntax

data = sqlfind(conn,pattern)

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

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,Name,Value) uses additional options specified by one or more name-value pair arguments. For example, 'Catalog',"cat" finds all table types in the "cat" 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
       Catalog               Schema                      Table                 Columns        Type 
    ______________    ____________________    ___________________________    ____________    ______

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

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

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 toystore_doc 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',"toystore_doc");

Display the first eight table types.

head(data)

ans=8×5 table
       Catalog               Schema                          Table                      Columns        Type 
    ______________    ____________________    ___________________________________    _____________    ______

    "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"
    "toystore_doc"    "information_schema"    "_pg_foreign_tables"                   {1×7  string}    "VIEW"
    "toystore_doc"    "information_schema"    "_pg_user_mappings"                    {1×7  string}    "VIEW"
    "toystore_doc"    "information_schema"    "administrable_role_authorizations"    {1×3  string}    "VIEW"
    "toystore_doc"    "information_schema"    "applicable_roles"                     {1×3  string}    "VIEW"
    "toystore_doc"    "information_schema"    "attributes"                           {1×31 string}    "VIEW"

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×7 string
    "foreign_table_catalog"    "foreign_table_schema"    "foreign_table_name"    "ftoptions"    "foreign_server_catalog"    "foreign_server_name"    "authorization_identifier"

Close the database connection.

close(conn)

Input Arguments

conn — PostgreSQL native interface database connection

connection object

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

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

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',"toystore_doc",'Schema',"dbo") returns information about table types, stored in the specified catalog and schema, that match the name of the table type with 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. A database can have multiple catalogs.

Example: Catalog = "toy_store"

Data Types: string | char

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. A database catalog can have numerous schemas.

Example: Schema = "dbo"

Data Types: string | char

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 cell array of character vectors.

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:

• Base table

• View

• Foreign table

• Local temporary

Version History

Introduced in R2020b