PROVE IT !!If you know it then PROVE IT !! Skill Proficiency Test

DYNAMIC QUERY TO FIND COUNT OF RECORDS FOR ALL TABLE IN WAREHOUSE

1 ) —– CREATE TEMPORARY TABLE

CREATE MULTISET TABLE ROWCT_SPR
(
DATABASENAME VARCHAR(50) CHARACTER SET LATIN CASESPECIFIC,
TABLENAME VARCHAR(50) CHARACTER SET LATIN CASESPECIFIC,
RECORDCOUNT BIGINT
)
PRIMARY INDEX ( DATABASENAME );
2 ) —– GENERATE DYNAMIC INSERT STATEMENT FOR COUNT OF RECORDS

SELECT ' INSERT INTO ROWCT_SPR SELECT ' || '''' ||
TRIM(DATABASENAME) || '''' || ',' || '''' ||
TRIM(TABLENAME) || '''' ||
', COUNT(*) FROM ' || TRIM(DATABASENAME)||'.'||TRIM(TABLENAME)|| ' ; '
FROM
(
SEL DATABASENAME,TABLENAME FROM DBC.TABLES
WHERE DATABASENAME IN <<<<—-INSERT THE EXCEPTION LIST—>>>>
) A

3 ) —– EXECUTE ALL THE DYNAMICALLY GENERATED INSERT STATMENT AND RETREIVE THE LIST FROM THE TABLE

Add a Comment

Your email address will not be published. Required fields are marked *