sqlfind
Find information about all table types in database
Description
returns information about all the Table Types in a database
where the specified character pattern appears in the name of a 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 in Database
Use an ODBC connection to find information about all database table types in a Microsoft® SQL Server® database.
Create an ODBC database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password.
datasource = 'MS SQL Server Auth'; conn = database(datasource,'','');
Check the database connection. If the Message
property is empty, then the connection is successful.
conn.Message
ans = []
Find information about all table types in the database.
data = sqlfind(conn,'');
Display information about the first three table types.
data(1:3,:)
ans = 3×5 table Catalog Schema Table Columns Type ___________ ____________________ _____________________ ___________ ______ 'toy_store' 'INFORMATION_SCHEMA' 'CHECK_CONSTRAINTS' {1×4 cell} 'VIEW' 'toy_store' 'INFORMATION_SCHEMA' 'COLUMNS' {1×23 cell} 'VIEW' 'toy_store' 'INFORMATION_SCHEMA' 'COLUMN_DOMAIN_USAGE' {1×7 cell} '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
Use an ODBC connection to find information about a database table in a Microsoft® SQL Server® database.
Create an ODBC database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password. The database contains the table productTable
.
datasource = 'MS SQL Server Auth'; conn = database(datasource,'','');
Check the database connection. If the Message
property is empty, then the connection is successful.
conn.Message
ans = []
Find information about any tables that contain the pattern product
in the table name. The sqlfind
function returns information about the table productTable
.
pattern = 'product';
data = sqlfind(conn,pattern)
data = 1×5 table Catalog Schema Table Columns Type ___________ ______ ______________ __________ _______ 'toy_store' 'dbo' 'productTable' {1×5 cell} 'TABLE'
data
contains these variables:
Catalog name
Schema name
Table name
Columns in the database table
Table type
Display the column names in productTable
.
data.Columns{:}
ans = 1×5 cell array Columns 1 through 4 {'productNumber'} {'stockNumber'} {'supplierNumber'} {'unitCost'} Column 5 {'productDescript…'}
Close the database connection.
close(conn)
Find Information About Table Types in Catalog and Schema
Use an ODBC connection to find information about all database table types in a Microsoft® SQL Server® database. Specify the database catalog and schema to search.
Create an ODBC database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password.
datasource = 'MS SQL Server Auth'; conn = database(datasource,'','');
Check the database connection. If the Message
property is empty, then the connection is successful.
conn.Message
ans = []
Find information about all table types in the toy_store
database catalog and the dbo
database schema. Use the 'Catalog'
name-value pair argument to specify the catalog. Use the 'Schema'
name-value pair argument to specify the schema.
data
is a table that contains information about all the table types in the specified catalog and schema.
data = sqlfind(conn,'','Catalog','toy_store','Schema','dbo');
Display the first eight table types.
head(data)
ans = 8×5 table Catalog Schema Table Columns Type ___________ ______ __________________ __________ _______ 'toy_store' 'dbo' 'DS17111713025590' {1×5 cell} 'TABLE' 'toy_store' 'dbo' 'DS17111713025699' {1×4 cell} 'TABLE' 'toy_store' 'dbo' 'DS22121715025751' {1×5 cell} 'TABLE' 'toy_store' 'dbo' 'DS22121715025879' {1×4 cell} 'TABLE' 'toy_store' 'dbo' 'DS22121715052820' {1×5 cell} 'TABLE' 'toy_store' 'dbo' 'DS22121715052941' {1×4 cell} 'TABLE' 'toy_store' 'dbo' 'DS26121710493780' {1×5 cell} 'TABLE' 'toy_store' 'dbo' 'DS26121710493818' {1×4 cell} '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×4 cell array {'productNumber'} {'Quantity'} {'Price'} {'inventoryDate'}
Close the database connection.
close(conn)
Input Arguments
conn
— Database connection
connection
object
Database connection, specified as an ODBC connection
object or JDBC connection
object created using the
database
function.
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 tables types in a database.
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','toy_store','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 for
table types, where the table type name partially or fully matches the text
in pattern
. The returned table has these
variables.
Variable | Description | Variable Data Type |
---|---|---|
Catalog | Catalog name where the database table type is stored | Cell array of character vectors |
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, which 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
Version History
Introduced in R2018a
MATLAB Command
You clicked a link that corresponds to this MATLAB command:
Run the command by entering it in the MATLAB Command Window. Web browsers do not support MATLAB commands.
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)