MATLAB Examples

Retrieve Database Metadata

This example shows how to retrieve database information using the connection object and the sqlfind function.

The example assumes that you are connecting to a Microsoft® SQL Server® database that contains a table named productTable.

Contents

Connect to 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 Catalogs and Schemas

Display the catalogs in the database by using the Catalogs property of the connection object.

conn.Catalogs
ans =

  1×4 cell array

    {'master'}    {'msdb'}    {'tempdb'}    {'toy_store'}

Display the first three schemas in the database by using the Schemas property of the connection object.

conn.Schemas{1:3}
ans =

    'dbo'


ans =

    'guest'


ans =

    'INFORMATION_SCHEMA'

Find Table Types

Find all table types in the database by using the sqlfind function with the connection object.

tables = sqlfind(conn,'');

Display the first three table types.

tables(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'

Find the table type of the table productTable.

tablename = 'productTable';
data = sqlfind(conn,tablename);
data.Type
ans =

  1×1 cell array

    {'TABLE'}

Find Table Columns

Find all columns in the database table productTable and display them.

data = sqlfind(conn,tablename);
data.Columns{:}
ans =

  1×5 cell array

  Columns 1 through 4

    {'productNumber'}    {'stockNumber'}    {'supplierNumber'}    {'unitCost'}

  Column 5

    {'productDescript…'}

Close Database Connection

close(conn)