sqlfind

Find information about all table types in 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 a 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 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