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: The DICTIONARY tables rely on data source support for their information. If the data source does not support the underlying function call, no rows are returned. For example, some data sources do not return information for the STATISTICS and COLUMN_STATISTICS dictionary tables. None of the data sources return DICTIONARY.COLUMNS information for FedSQL views.

DICTIONARY queries that do not specify a WHERE clause can take a long time to process, especially if there are a larger number of tables in the data source.

Last updated: February 23, 2017