Main Content

sqlfind

Find information about all table types in PostgreSQL database

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,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

collapse all

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)

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

collapse all

PostgreSQL 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

Name-Value Pair Arguments

Specify optional comma-separated pairs of Name,Value arguments. Name is the argument name and Value is the corresponding value. Name must appear inside quotes. You can specify several name and value pair arguments in any order as Name1,Value1,...,NameN,ValueN.

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.

Database catalog name, specified as the comma-separated pair consisting of 'Catalog' and 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: 'Catalog','toy_store'

Data Types: char | string

Database schema name, specified as the comma-separated pair consisting of 'Schema' and a character vector or string scalar. 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: 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 cell array of character vectors.

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:

  • Base table

  • View

  • Foreign table

  • Local temporary

Introduced in R2020b