Ace and Jet Special Queries

Queries that return information.

Syntax

Details

Examples

Example 1: Retrieve a Specific Rowset

Example 2: Retrieve Specific Data Types

Syntax

ACE | JET : schema-rowset'parameter-1, ..., parameter-n

Required Arguments

SAS/ACCESS Interface to PC Files supports a number of special queries that return information such as available tables, columns, and procedures.

ACE | JET :: required to distinguish special queries from regular queries.

schema-rowset: the specific schema rowset that is being called. The valid schema rowsets are listed below.

"parameter-1, ..., parameter-n"

Separate parameters from one another by commas.
All parameters are optional.
Quotation marks are required.
If you specify some, but not all, parameters within an argument, use commas to indicate the omitted parameters.

Details

ACE|JET:: CHECK_CONSTRAINTS
returns the check constraints that are defined in the database file.

ACE|JET::COLUMNS<"table-name"> , <"column-name">
returns the columns of the tables that are defined in the database file.

ACE|JET::CONSTRAINT_COLUMN_USAGE<"table-name"> ,<"column-name">
returns the columns that are used by referential constraints, unique constraints,
check constraints, and assertions that are defined in the database file.

ACE|JET::FOREIGN_KEYS<"primary-key-table-name"> , <"foreign-key-table-name">
returns the foreign key columns that are defined in the database file.

ACE|JET::INDEXES<"index-name"> , <"table-name">
returns the indexes that are defined in the database file.

ACE|JET::KEY_COLUMN_USAGE<"constraint-name"> , <"table-name"> ,
<"column-name">
returns the key columns that are defined in the database file.

ACE|JET::PRIMARY_KEYS<"table-name">
returns the primary key columns that are defined in the database file.

ACE|JET::PROCEDURES <"procedure-name">
returns the procedures that are defined in the database file.

ACE|JET::PROVIDER_TYPES
returns information about the base data types that are supported by the Jet data provider.

ACE|JET::REFERENTIAL_CONSTRAINTS <"constraint-name">
returns the referential constraints that are defined in the database file.

ACE|JET:: STATISTICS <"table-name">
returns the statistics that are defined in the database file.

ACE|JET::TABLE_CONSTRAINTS <"constraint-name"> , <"table-name"> ,
<"constraint-type">
returns the table constraints that are defined in the database file.

ACE|JET::TABLES <"table-name"> , <"table-type">
returns the tables that are defined in the database file.

ACE|JET::VIEWS <"table-name">
returns the viewed tables that are defined in the database file.

Examples

Example 1: Retrieve a Specific Rowset

Retrieve a rowset that displays all tables in the NorthWind database.

PROC SQL;
  * CONNECT TO access database;
  CONNECT TO Access AS db (PATH='c:\NorthWind.mdb');
  * list all tables including system tables and pass-through;
  SELECT * FROM CONNECTION TO db(jet::tables);
  * list table name and type where table type is TABLE only;
  SELECT table_name, table_type from CONNECTION TO db(jet::tables ,"TABLE";
  DISCONNECT FROM db;`
QUIT;

Example 2: Retrieve Specific Data Types

Retrieve all data types that the Ace or Jet provider for Microsoft Access supports.

PROC SQL;
  CONNECT TO access (PATH='c:\NorthWind.mdb');
  SELECT * FROM CONNECTION TO access(jet::provider_types);
QUIT;