Table Structure
This feature is not supported on the Wolfram Cloud.

Table Description

This section discusses commands that get information about database tables.

If you find that the examples in this section do not work as shown, you may need to install or restore the example database with the package, as described in "Using the Example Databases".

SQLTableNames[conn]list all table names within a data source
SQLTableNames[conn,name,opts]list all table names that match name within a data source
SQLTables[conn]list all tables within a data source
SQLTables[conn,name,opts]list all tables that match name within a data source
SQLTableInformation[conn]list all table information within a data source
SQLTableInformation[conn,name,opts]list all table information for tables that match name within a data source
SQLTableTypeNames[conn]list the types of table supported in this data source

Functions for retrieving information about tables.

This loads DatabaseLink and connects to the publisher database.

In[1]:=
Click for copyable input

SQLTableNames returns a list of the names of the tables within the connection.

In[2]:=
Click for copyable input
Out[2]=

SQLTables returns a list of SQLTable expressions. These hold information about the tables in a database.

In[3]:=
Click for copyable input
Out[3]=

SQLTableInformation returns more complete information about tables.

In[4]:=
Click for copyable input
Out[4]//TableForm=

With each function, you can filter the names of the tables by providing a string to match as the second parameter. An important point is that this filtering is done on the database server, which leads to significant speed enhancements. The following example searches for a table named AUTHORS. If no such table existed, the result would be an empty list.

In[5]:=
Click for copyable input
Out[5]=

It is also possible to give metacharacters to match more than one table. The metacharacters are , which matches zero or more characters, and , which matches a single character. The following command returns the names of all tables that start with TITLE.

In[6]:=
Click for copyable input
Out[6]=

SQLTables, SQLTableNames, and SQLTableInformation take a number of options.

option name
default value
"TableType""TABLE"type of table to be returned
"Catalog"Nonedatabase catalog to use
"Schema"Nonedatabase schema to use
"ShowColumnHeadings"Falsewhether to return headings with the results (SQLTableInformation option only)

The option selects which type of table is returned. Typically, it is the tables of type TABLE that are of interest, and by default DatabaseLink table functions only return information on these. You can use SQLTableTypeNames to find all the different types of tables in your data source.

In[7]:=
Click for copyable input
Out[7]=

If you want to see all the tables in the data source, you can use the result of SQLTableTypeNames with the option . This is demonstrated in the following.

In[8]:=
Click for copyable input
Out[8]=

The option can be used with SQLTableInformation to return the column headings.

In[9]:=
Click for copyable input
Out[9]//TableForm=

This closes the connection.

In[10]:=
Click for copyable input
Out[10]=

If the database was designed with particular schema and catalogs, you can also select tables by using the and options.

Table Representation

SQLTable expressions hold information about the tables in a database.

If you find that the examples in this section do not work as shown, you may need to install or restore the example database with the package, as described in "Using the Example Databases".

SQLTable[table,opts]expression that represents an SQL table

An example demonstrating SQLTable expressions follows. This loads DatabaseLink and connects to the demo database.

In[11]:=
Click for copyable input

The option is used to select the type of the table in the database.

option name
default value
"TableType""TABLE"type of the table

Now SQLTables is used to return a list of the tables in the database; they are returned as SQLTable expressions. In this example, a pattern is given to match the names of the tables, and the option is set to return tables of all types.

In[13]:=
Click for copyable input
Out[13]=

This closes the connection.

In[14]:=
Click for copyable input

SQLTable expressions can also be used in commands, as shown in "Selecting Data".