Archives of the TeradataForum

Message Posted: Tue, 26 Jul 2016 @ 12:20:11 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Sandeep,

If statistics for all tables were collected and are current in a database, you can use the following query:

     SEL DATABASENAME,
         SUM(ROWCOUNT)
     FROM DBC.TABLESTATSV
     WHERE DATABASENAME = 'DatabaseName'
     GROUP BY 1;

If not, then use the following query in the following sequence of steps:

STEP 1: (Execute the following query)

     SEL DERIVED.COUNT_QUERY || CASE WHEN ROW# <> 1 THEN ' UNION ALL' ELSE '' END
     FROM(
        SEL 'SEL CAST(' || '''' || TRIM(DATABASENAME) || '''' || ' AS
        VARCHAR(1000)) DATABASENAME' ||  ',' || 'CAST(' || '''' || TRIM(TABLENAME)
        || '''' || ' AS VARCHAR(1000)) TABLENAME' || ', CAST(COUNT(*) AS
        DECIMAL(32,0)) ROWCOUNT FROM ' || '"' || TRIM(DATABASENAME) || '"' || '.' ||
        '"' || TRIM(TABLENAME) || '"' COUNT_QUERY
        , ROW_NUMBER() OVER(ORDER BY COUNT_QUERY DESC) ROW#
        FROM DBC.TABLESV
        WHERE DATABASENAME = 'DatabaseName'
        AND TABLEKIND = 'T'
        ) DERIVED ORDER BY ROW# DESC;

STEP 2: (Copy the result set of the previous query in the subquery of the following as a derived table)

     SEL DATABASENAME,
         SUM(ROWCOUNT)
     FROM (
     -- INSERT SET QUERY HERE
     ) DERIVED
     GROUP BY 1;

Best regards,

Sherwin

	<Prev	Next>		<<First	<Prev	Next>	Last>>