SQL Tables

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 DatabaseExamples` package.

Functions for retrieving information about tables.

This loads DatabaseLink and connects to the publisher database.

In[1]:=

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

In[3]:=

Out[3]=

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

In[4]:=

Out[4]=

In addition, SQLTableInformation returns more complete information about tables.

In[5]:=

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[6]:=

Out[6]=

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 gets the names of all tables that start with TITLE.

In[7]:=

Out[7]=

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

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

In[8]:=

Out[8]=

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

In[9]:=

Out[9]=

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

In[10]:=

This closes the connection.

In[11]:=

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