sqlfind

Find information about all table types in PostgreSQL database

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

'Catalog' — Database catalog name

character vector | string scalar

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

'Schema' — Database schema name

character vector | string scalar

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

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