Table Structure
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
DatabaseExamples` 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.
SQLTableNames returns a list of the names of the tables within the connection.
| Out[3]= |  |
SQLTables returns a list of
SQLTable expressions. These hold information about the tables in a database.
| Out[4]= |  |
SQLTableInformation returns more complete information about tables.
Out[5]//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.
| 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 command returns the names of all tables that start with
TITLE.
| Out[7]= |  |
| | |
| "TableType" | "TABLE" | type of table to be returned |
| "Catalog" | None | database catalog to use |
| "Schema" | None | database schema to use |
| "ShowColumnHeadings" | False | whether to return headings with the results (SQLTableInformation option only) |
The option
"TableType" 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.
| Out[8]= |  |
If you want to see all the tables in the data source, you can use the result of
SQLTableTypeNames with the option
"TableType". This is demonstrated in the following.
| Out[9]= |  |
The option
"ShowColumnHeadings" can be used with
SQLTableInformation to return the column headings.
Out[10]//TableForm= |
| |  |
This closes the connection.
If the database was designed with particular schema and catalogs, you can also select tables by using the
"Catalog" and
"Schema" 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
DatabaseExamples` 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.
The
"TableType" option is used to select the type of the table in the database.
| | |
| "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
"TableType" option is set to return tables of all types.
| Out[3]= |  |
This closes the connection.
SQLTable expressions can also be used in commands as shown in
"Selecting Data".
