sqlfind
Description
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 data
= sqlfind(conn
,pattern
)SELECT * FROM
information_schema.tables
.
uses additional options specified by one or more name-value pair arguments. For example,
data
= sqlfind(conn
,pattern
,Name,Value
)'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
Comando de MATLAB
Ha hecho clic en un enlace que corresponde a este comando de MATLAB:
Ejecute el comando introduciéndolo en la ventana de comandos de MATLAB. Los navegadores web no admiten comandos de MATLAB.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list:
How to Get Best Site Performance
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)