Table counts in a database

Database

Table counts in a database

Please guide/help me writing a macro or procedure. Below is my scenario

I have few tables in a database which starts with ‘dev%’ and same table with ‘test%’. Am using these for my testing purpose.

Sample table name: 

devdatabse.dev_customers

devdatabse.test_customers

I have 500 tables in the database. My scenario is to find out the row count of each table present in that database which starts with ‘dev%’  and ‘test%’ 

Sample data in excel file: 

Tablename dev_count test_count  Difference

Customers 100 99 1

The output should be exported to a csv file

I see few sample queries in forum but those sql queres i hve to run it manually everytime i do this task.. So i wuld like to automate it through a macro or procedure

2 REPLIES
Teradata Employee

Re: Table counts in a database

This is not a procedure or macro. This is a dynamic SQL so you should execute the answer of this query. Firt of all you shoul replace the string '<your_db>' and write yours.

select 'select '''||trim(corto)||''' as tabla, DEV.filas as DEV, TES.filas as TEST, DEV.filas-TES.filas
from
(Select count(1) as filas from <your_db>.dev_'||trim(corto)||')as DEV
cross join
(Select count(1) as filas from <your_db>.test_'||trim(corto)||')as TES union all'
from (
select a.tablename,
case when tablename like 'dev_%' then substr(tablename,5)
when tablename like 'test_%' then substr(tablename,6)
else null end as corto
from dbc.tables a
where tablename like any('dev/_%', 'test/_%') escape '/'
and databasename = '<your_db>')as TMP
group by 1
having count(distinct tablename)=2

Re: Table counts in a database

Thanks for you response. This was very helpful