Teradata Table Stat Extraction for Schema Optimization Recommendations

This topic provides detailed steps for extracting table statistics that typically includes number of rows in the table, distinct rows in the table, table size, etc.

In this Topic:

Prerequisites
Extraction

Prerequisites

The Teradata table statistics should be updated. In case it’s not updated, execute the following command on all the tables in the database.

COLLECT STATISTICS ON t1 COLUMN (x1,y1);

Extraction

There can be multiple means to extract the tables stats from your Teradata instance. You can use anyone given below.

BTEQ: Execute query using the BTEQ utility and save results as a CSV file
Alternatively, you can use any tool that supports Teradata connection. For example, Teradata SQL Assistant, SQL Developer, DBeaver, etc.
Another way could be through the JDBC or ODBC driver – Use Teradata JDBC/ODBC driver if you want to export the required stats using any programming language

Teradata Table Stat Extraction Query

SELECT cols.databasename AS DATABASE_NAME,

cols.tablename AS Table_Name,

Cast(alsp.currentperm AS BIGINT) AS TABLE_SIZE_BYTES,

” AS NUM_ROWS,

cols.columnname AS Column_Name,

” AS num_unique_val

FROM dbc.columns AS cols

JOIN ( select databasename,tablename, cast(sum (currentperm) as bigint) as currentperm from dbc.allspace group by 1 ,2 ) alsp

ON cols.tablename = alsp.tablename and cols.databasename = alsp.databasename

WHERE cols.tablename <> ‘All’;

Example: