Column Structure
Column Description
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, as described in
"Using the Example Databases".
| SQLColumnNames[conn] | list all column names within a data source |
| SQLColumnNames[conn,name,opts] | list all column names that match name within a data source |
| SQLColumns[conn] | list all columns within a data source |
| SQLColumns[conn,name,opts] | list all columns that match name within a data source |
| SQLColumnInformation[conn] | list all column information for tables within a data source |
| SQLColumnInformation[conn,name,opts] | list all column information for columns that match name within a data source |
Functions for retrieving information about columns.
This loads
DatabaseLink and connects to the
demo database.
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.
| Out[3]= |  |
It is possible to use metacharacters that will match names. The metacharacters are '%' for zero or more characters and '_' for a single character. The following command matches columns in tables that have names starting with "SA".
| Out[4]= |  |
SQLColumns returns a list of
SQLColumn expressions.
SQLColumn expressions are sometimes useful for structural arguments in database commands, as described in
Argument Sequences in SQL-Style Queries, because they contain information on the table name, column name, data type, whether an entry can be set to
Null, and the data length.
| Out[5]= |  |
SQLColumnInformation returns more information about the columns.
Out[6]//TableForm= |
| |  |
You can filter the names of the columns by providing a list of metacharacters to match the table and column names. The following command searches in all tables to return all columns that start with V.
| Out[7]= |  |
You can find all the columns in a single table by specifying the table name.
| Out[8]= |  |
You can also give a
SQLTable argument.
| 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.
| Out[10]= |  |
In addition, you can give an
SQLColumn argument.
| Out[11]= |  |
| | |
| "Catalog" | None | database catalog to use |
| "Schema" | None | database schema to use |
| "ShowColumnHeadings" | False | whether to return headings with the results (SQLColumnInformation option only) |
SQLColumns, SQLColumnNames, and SQLColumnInformation options.
The option
"ShowColumnHeadings" can be used with
SQLColumnInformation to return the column headings.
Out[12]//TableForm= |
| |  |
This closes the connection.
If the database was designed with particular schema and catalogs, you can also select columns by using the
"Catalog" and
"Schema" options.
Column Representation
SQLColumn expressions hold information about the columns 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
DatabaseExamples` package, as described in
"Using the Example Databases".
| SQLColumn[{table,col},opts] | expression that represents a column in an SQL table |
Object for representing a column.
SQLColumn accepts a number of options.
| | |
| "DataTypeName" | None | type of the entry |
| "Nullable" | None | whether the entry can be null |
| "DataLength" | None | maximum length for variable length data |
SQLColumn options.
Here is an example demonstrating
SQLColumn expressions. This loads
DatabaseLink and connects to the
demo database.
SQLColumns returns a list of the columns in the database as
SQLColumn expressions. In this example a pattern is given to pick out just the
SAMPLETABLE1 table.
| Out[3]= |  |
This closes the connection.
SQLColumn expressions can also be used in commands as discussed in
"Selecting Data" and
"Creating Tables".
"Creating Tables" discusses one particularly important use.
