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 "DatabaseLink`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.
SQLTables returns a list of SQLTable expressions. These hold information about the tables in a database.
SQLTableInformation returns more complete information about tables.
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.
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.
SQLTables, SQLTableNames, and SQLTableInformation take a number of options.
option name | default value | |
| "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.
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.
The option "ShowColumnHeadings" can be used with SQLTableInformation to return the column headings.
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 "DatabaseLink`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.
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.
SQLTable expressions can also be used in commands, as shown in "Selecting Data".