Sometimes you want to know whether an existing table has any indexes. To display a CREATE INDEX statement in the SAS log for each index that is defined for one or more specified tables, you can use the DESCRIBE TABLE statement. (The DESCRIBE TABLE statement also writes a CREATE TABLE statement to the SAS log for each specified table.)

If a specified table has no indexes, a CREATE INDEX statement does not appear.

Earlier in this chapter, the following code was used to create a unique composite index named daily on the columns FlightNumber and Date in the temporary table Marchflights.

proc sql;
   create unique index daily
      on work.marchflights(flightnumber,date);

The following DESCRIBE TABLE statement writes a CREATE INDEX statement to the SAS log (after the CREATE TABLE statement) for the table Marchflights:

proc sql;
   describe table marchflights;

NOTE: SQL table WORK.MARCHFLIGHTS was created like:

create table WORK.MARCHFLIGHTS( bufsize=8192 )
  (
   Date num format=DATE9. informat=DATE9.,
   DepartureTime num format=TIME5. informat=TIME5.,
   FlightNumber char(3),
   Origin char(3),
   Destination char(3),
   Distance num,
   Mail num,
   Freight num,
   Boarded num,
   Transferred num,
   NonRevenue num,
   Deplaned num,
   PassengerCapacity num
  );
create unique index daily on WORK.MARCHFLIGHTS(FlightNumber,Date);

If the table Marchflights had no index defined, no CREATE INDEX statement would appear in the SAS log.

The DESCRIBE TABLE statement is only one of several methods that can be used to list information about indexes that are defined on a table. One alternative is to query the special table Dictionary.Indexes, which contains information about indexes that are defined for all tables that are known to the current SAS session. (Dictionary.Indexes is one of many read-only dictionary tables that are created at PROC SQL initialization. These tables contain information about SAS libraries, SAS macros, and external files that are in use or available in the current SAS session.)

You can also use other SAS procedures, such as PROC CONTENTS and PROC DATASETS, to generate a report that contains information about indexes.