DICTIONARY Tables

FedSQL DICTIONARY tables are similar to but different from Base SAS DICTIONARY tables. A FedSQL DICTIONARY table is a Read-only table that contains information about columns, tables, and catalogs, and statistics about tables and their associated indexes.

The following table describes the DICTIONARY tables that are available. For a complete description of all the columns in the DICTIONARY tables, see DICTIONARY Table Descriptions.

DICTIONARY Tables
DICTIONARY table	Description
CATALOGS	contains information about catalogs in the current connection.
COLUMNS	contains information about columns in all known tables.
COLUMN_STATISTICS	contains table and index statistics that are based on the columns in the tables.
STATISTICS	contains table and index statistics that are associated with the tables.
TABLES	contains information about table types and a list of table, catalog, or schema names of tables in the data source.

You can query DICTIONARY tables the same way you query any other table. For all DICTIONARY tables except CATALOG, you can include subsetting with a WHERE clause and ordering the results. Identifiers should be quoted. The following queries are examples.

Tip

FedSQL sends table and column names to the underlying databases as uppercase when the table is created, unless you quote the names in the CREATE TABLE statement or the data source supports only lowercase storage. DICTIONARY queries are case-sensitive. Specify table and column names as uppercase unless you know them to be otherwise.

select * from dictionary.catalogs where catalog='SALECAT';

select * from dictionary.columns where table_name='YEAREND';

select * from dictionary.tables;

Note: Some of the DICTIONARY tables (for example, STATISTICS and COLUMN_STATISTICS) rely on table driver support for their information. If the table driver does not support the underlying function call, no rows are returned.