Teradata get Table DDL easily with the table name -

Contents hide

4 Method 2 – Table DDL Using show select

Getting the full DDL of a table in Teradata is the most common requirement in order to look at the table structures. DBAs does this often to see if there is any way to improve the way the index is created. This will come in handy while debugging the performance of queries involving such tables. Now, there are two ways to get the DDL using the table name.

Let us first create a sample table to understand this better

CREATE TABLE TESTDB.DDLExample
(
   ID INT, 
   NAME VARCHAR(50), 
   DOB DATE
)UNIQUE PRIMARY INDEX(ID);

Now that we have created the table, let see how to get the DDL of the table with the database and table name using the first method.

Method 1 – Table DDL Using show table

Check the below syntax with the show table in teradata to get the structure of the table.

Syntax

SHOW TABLE DATABASE.TABLENAME;

Example

SHOW TABLE TESTDB.DDLExample;

The above command will give the full structure of the table “DDLExample” along with the default table properties set by the Teradata system. The result of the above command is shown below. This is by using the show table in Teradata.

CREATE SET TABLE pp_scratch.DDLExample ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     MAP = TD_MAP1
     (
      ID INTEGER,
      NAME VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      DOB DATE FORMAT 'YYYY/MM/DD'
     )UNIQUE PRIMARY INDEX ( ID );

You can see that there are more properties like CHECKSUM, MAP, FALLBACK, MERGEBLOCKRATIO, etc added by default by the Teradata system

Method 2 – Table DDL Using show select

Syntax

SHOW SEL/SELECT * from  DATABASE.TABLENAME;

Example

SHOW SELECT * FROM  TESTDB.DDLExample;

The above command will give the full structure of the table “DDLExample”. Here, instead of “SELECT *” we can also use “SEL *”. As mentioned earlier, the result will include the default properties for the table set by the Teradata system. Check the result below

CREATE SET TABLE pp_scratch.DDLExample ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     MAP = TD_MAP1
     (
      ID INTEGER,
      NAME VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      DOB DATE FORMAT 'YYYY/MM/DD'
     )UNIQUE PRIMARY INDEX ( ID );