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 "DatabaseLink`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.
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".
SQLColumns returns a list of SQLColumn expressions. SQLColumn expressions are sometimes useful for structural arguments in database commands, as described in "SQLExecute: 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.
SQLColumnInformation returns more information about the columns.
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".
You can find all the columns in a single table by specifying the table name.
You can also give an SQLTable argument.
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 Wolfram Language part notation, entered with [[All,2]], to extract just the second elements.
In addition, you can give an SQLColumn argument.
SQLColumns, SQLColumnNames, and SQLColumnInformation take a number of options.
option name | default value | |
| "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.
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 "DatabaseLink`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.
| option name | default value | |
| "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.
SQLColumn expressions can also be used in commands, as discussed in "Selecting Data" and "Creating Tables". "Creating Tables" discusses one particularly important use.