Teradata Tidbits

Record Count for all Tables in a Database

select 'select count (1), ''' || trim(tablename) || ''' (varchar(30)) from ' || trim(databasename) || '.' || trim (tablename) || ' union ' from dbc.tables where databasename = 'my_database';

Recreate Collect Statistics Script (requires privs)

SELECT  'collect statistics on ' || TRIM ( databasename ) || '.' || TRIM ( tablename ) 
        || 
                Case 
                        When    indextype = 'M' Then ' column (' 
                        Else    ' index (' 
                End     
        || indexcols || ');' 
FROM    ( 
SELECT  databasename , tablename , indexnumber , indextype, MAXIMUM ( 
                CASE 
                        WHEN    columnposition = 1 THEN TRIM ( columnname ) 
                END     ) || MAXIMUM ( 
                CASE 
                        WHEN    columnposition = 2 THEN ',' || TRIM ( columnname ) 
                        ELSE    '' 
                END     ) || MAXIMUM ( 
                CASE 
                        WHEN    columnposition = 3 THEN ',' || TRIM ( columnname ) 
                        ELSE    '' 
                END     ) || MAXIMUM ( 
                CASE 
                        WHEN    columnposition = 4 THEN ',' || TRIM ( columnname ) 
                        ELSE    '' 
                END     ) || MAXIMUM ( 
                CASE 
                        WHEN    columnposition = 5 THEN ',' || TRIM ( columnname ) 
                        ELSE    '' 
                END     ) || MAXIMUM ( 
                CASE 
                        WHEN    columnposition = 6 THEN ',' || TRIM ( columnname ) 
                        ELSE    '' 
                END     ) || MAXIMUM ( 
                CASE 
                        WHEN    columnposition = 7 THEN ',' || TRIM ( columnname ) 
                        ELSE    '' 
                END     ) || MAXIMUM ( 
                CASE 
                        WHEN    columnposition = 8 THEN ',' || TRIM ( columnname ) 
                        ELSE    '' 
                END     ) AS indexcols 
FROM    ( 
SELECT  c.databasenamei AS databasename 
,       b.tvmnamei AS tablename 
,       d.indexnumber AS indexnumber 
,       d.indextype AS indextype 
,       d.fieldposition AS columnposition 
,       a.fieldname AS columnname 
FROM    dbc.tvfields a 
,       dbc.tvm b 
,       dbc.dbase c 
,       dbc.indexes d 
WHERE   a.tableid = b.tvmid 
        AND     b.databaseid = c.databaseid 
        AND     d.fieldid = a.fieldid 
        AND     d.tableid = b.tvmid 
        AND     d.databaseid = c.databaseid 
        AND     ( d.databaseid , d.tableid , d.indexnumber )  IN ( 
SELECT  databaseid , tableid , indexnumber 
FROM    dbc.indexes 
WHERE   fieldposition = 1 
        AND     indexstatistics IS NOT NULL ) 
        AND     databasename = 'MY_DBNAME' ) a 
GROUP   BY 1 , 2 , 3, 4 ) b 
UNION 
SELECT  'collect statistics on ' || TRIM ( databasename ) || '.' || TRIM ( tablename ) || ' column (' || indexcols || ');'

FROM    ( 
SELECT  databasename , tablename , TRIM ( columnname ) AS indexcols 
FROM    ( 
SELECT  c.databasenamei AS databasename , b.tvmnamei AS tablename , a.fieldname AS columnname 
FROM    dbc.tvfields a , dbc.tvm b , dbc.dbase c 
WHERE   a.tableid = b.tvmid 
        AND     b.databaseid = c.databaseid 
        AND     a.fieldstatistics IS NOT NULL 
        AND     databasename = 'MY_DBNAME' ) a 
GROUP   BY 1 , 2 , 3 ) b ;