The DESCRIBE TABLE statement writes a CREATE TABLE statement to the SAS log for the table specified in the DESCRIBE TABLE statement, regardless of how the table was originally created (for example, with another programming language). The column definitions returned by the DESCRIBE TABLE statement show the column’s native data type. If indexes are defined on columns in the table, then CREATE INDEX statements for those indexes are written to the SAS log. The CREATE TABLE statement also includes any integrity constraints that are defined for the specified table. For SAS data sets, primary key integrity constraints are described by a CREATE UNIQUE INDEX statement. Foreign key variables that reference a primary key constraint are not displayed as part of the primary key constraint's DESCRIBE TABLE output.

The FedSQL language supports most of the data types used by the data sources that it supports, but not all. Refer to Data Type Reference for a listing of the data types that FedSQL supports for each data source. If you have a need to create a table with a data type that FedSQL does not support directly, use the EXECUTE statement to make the request.

Examples

Example 1: Information Returned for a SAS Data Set

Consider a SAS data set created with the SAS DATA step:

data myfiles.employees;
    input IdNum $4. +2 LName $11. FName $11. JobCode $3.
          +1 Salary 5. +1 Phone $12.;

The DESCRIBE TABLE statement writes the following information to the SAS log:

describe table myfiles.employees;

CREATE TABLE MYFILES."MYFILES".EMPLOYEES (
     "IdNum"     CHARACTER(4),
     "LName"     CHARACTER(11),
     "FName"     CHARACTER(11),
     "JobCode"     CHARACTER(3),
     "Salary"     DOUBLE,
     "Phone"     CHARACTER(12)
);

Example 2: Information Returned for a Supported Data Type

Consider an Oracle table created by using FedSQL with the following statement:

CREATE TABLE ORACLE.FOO1 (COL1 INTEGER, COL2 VARHCHAR(10);

Oracle does not support the INTEGER data type. The FedSQL language converts the INTEGER data type to Oracle’s NUMBER data type when writing data to Oracle.

DESCRIBE TABLE oracle.foo1;

CREATE TABLE "ORACLE"."SCOTT".FOO1 (
     COL1     NUMBER,
     COL2     VARCHAR2(10))

Example 3: Information Returned For A Data Type For Which FedSQL Has Limited Support

Consider a DB2 table created with the following statement by using the DB2 driver natively:

CREATE TABLE dlfoo1 (col1 CLOB);

FedSQL has limited support for the CLOB data type. It does not currently allow creation of columns of the CLOB data type, but it does have some support for reading CLOB columns. The DESCRIBE TABLE statement writes the following information to the SAS log:

DESCRIBE TABLE dlfoo1;

CREATE TABLE "DB2"."DBITEST".DLFOO1 (
     COL1     CLOB
);

Example 4: Information Returned for Teradata

Consider a Teradata table created by using FedSQL:

create table x.customer (item_name char(30), cost decimal(10,2), delivered date);

The DESCRIBE TABLE statement returns a CREATE TABLE statement that describes the columns in the table. It also returns a CREATE INDEX statement, although an index was not explicitly defined. The index is a FedSQL abstraction. Do not use it to re-create the table.

describe table x.customer;

CREATE TABLE X."dmvdq1".CUSTOMER (
     ITEM_NAME     CHAR(60) DEFAULT NULL,
     COST     DECIMAL DEFAULT NULL,
     DELIVERED     DATE DEFAULT NULL
);
CREATE INDEX NONAME1 ON X."dmvdq1".CUSTOMER(ITEM_NAME);

DESCRIBE TABLE Statement

Syntax

Arguments

catalog

schema

table

Details

Examples

Example 1: Information Returned for a SAS Data Set

Example 2: Information Returned for a Supported Data Type

Example 3: Information Returned For A Data Type For Which FedSQL Has Limited Support

Example 4: Information Returned for Teradata

See Also