SQL Columns

This section discusses commands that get information about database columns. 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 columns.

This loads DatabaseLink and connects to the demo database.

In[1]:=

SQLColumnNames returns a list of the column names within a database as a list of pairs of table and column names. For HSQLDB it returns information from many of the SYSTEM tables.

In[3]:=

Out[3]=

It is possible to use metacharacters that will match names. As mentioned in the section SQL Tables, the metacharacters are '%' for zero or more characters and '_' for a single character. The following matches columns that are in tables that have names starting in SA.

In[4]:=

Out[4]=

SQLColumns returns a list of SQLColumn expressions. An SQLColumn expression is sometimes useful for structural arguments in database commands. This is because they contain information on the table name, column name, data type, whether an entry can be set to Null, and the data length.

In[5]:=

Out[5]=

SQLColumnInformation returns more information about the columns.

In[6]:=

You can filter the names of the columns by providing a list of metacharacters to match the table and column names. The following searches in all tables to return all columns that start with V.

In[7]:=

Out[7]=

You can find all the columns in a single table by specifying the table name.

In[8]:=

Out[8]=

You can also give a SQLTable argument.

In[9]:=

Out[9]=

SQLColumnNames returns a list where each entry is a list of the table name and the column names. If you want a list of just the column names you can use Mathematica part notation, entered with [[All, 2]], to extract just the second elements.

In[10]:=

Out[10]=

In addition you can also give an SQLColumn argument.

In[11]:=

Out[11]=

SQLColumns, SQLColumnNames, and SQLColumnInformation take a number of options.

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

In[12]:=

This closes the connection.

In[13]:=

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