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 ;