I have been stuck into a question. The question is I want to get all Table name with their Row Count from Teradata.
I have this query which gives me all View Name from a specific Schema.
I ] SELECT TableName FROM dbc.tables WHERE tablekind='V' AND databasename='SCHEMA' order by TableName;
& I have this query which gives me row count for a specific Table/View in Schema.
II ] SELECT COUNT(*) as RowsNum FROM SCHEMA.TABLE_NAME;
Now can anyone tell me what to do to get the result from Query I (TableName)
and put it into QUERY II (TABLE_NAME)
You help will be appreciated.
Thanks in advance,
Vrinda
This is a SP to collect row counts from all tables within a database, it's very basic, no error checking etc.
It shows a cursor and dynamic SQL using dbc.SysExecSQL or EXECUTE IMMEDIATE:
CREATE SET TABLE RowCounts
(
DatabaseName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
TableName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
RowCount BIGINT,
COllectTimeStamp TIMESTAMP(2))
PRIMARY INDEX ( DatabaseName ,TableName )
;
REPLACE PROCEDURE GetRowCounts(IN DBName VARCHAR(30))
BEGIN
DECLARE SqlTxt VARCHAR(500);
FOR cur AS
SELECT
TRIM(DatabaseName) AS DBName,
TRIM(TableName) AS TabName
FROM dbc.Tables
WHERE DatabaseName = :DBName
AND TableKind = 'T'
DO
SET SqlTxt =
'INSERT INTO RowCounts ' ||
'SELECT ' ||
'''' || cur.DBName || '''' || ',' ||
'''' || cur.TabName || '''' || ',' ||
'CAST(COUNT(*) AS BIGINT)' || ',' ||
'CURRENT_TIMESTAMP(2) ' ||
'FROM ' || cur.DBName ||
'.' || cur.TabName || ';';
--CALL dbc.sysexecsql(:SqlTxt);
EXECUTE IMMEDIATE sqlTxt;
END FOR;
END;
If you can't create a table or SP you might use a VOLATILE TABLE (as DrBailey suggested) and run the INSERTs returned by following query:
SELECT
'INSERT INTO RowCounts ' ||
'SELECT ' ||
'''' || DatabaseName || '''' || ',' ||
'''' || TableName || '''' || ',' ||
'CAST(COUNT(*) AS BIGINT)' || ',' ||
'CURRENT_TIMESTAMP(2) ' ||
'FROM ' || DatabaseName ||
'.' || TableName || ';'
FROM dbc.tablesV
WHERE tablekind='V'
AND databasename='schema'
ORDER BY TableName;
But a routine like this might already exist on your system, you might ask you DBA. If it dosn't have to be 100% accurate this info might also be extracted from collected statistics.