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 ;