sqlfind

Find information about all table types in database

collapse all in page

Syntax

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

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

Open Script

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)
Open Script

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)
Open Script

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

collapse all

Database connection, specified as a connection object created with the database function.

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

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 for table types, where the table type name partially or fully matches the text in pattern. The returned table has these variables.

VariableDescriptionVariable Data Type
CatalogCatalog name where the database table type is storedCell array of character vectors
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, 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

See Also

| | |

Topics

Introduced in R2018a

Database Toolbox Documentation

Support