Database Statistics

Database

Database Statistics

# records by table name without having to query the individual tables.

All,

I am a busines user and new to Teradata . I in the process of creating a datamart of analytics purposes and we have a very large teradata datawarehouse organised by clients - so 100s of tables .

Please let me know how do i get a summary of :

# records by table name without having to query the individual tables.

I read about COLLECT STATISTICS command but it appears like giveing statistics within objects . Please help.

4 REPLIES
Enthusiast

Re: Database Statistics

If I understood your intention correctly, then this is one of the ways you can get around.

-- Step 1 create a volatile table to store tablenames and record counts.

CREATE VOLATILE TABLE TBLRECCNT(TABLENAME VARCHAR(30), RECCNT INTEGER) ON COMMIT PRESERVE ROWS;

-- Step 2 run this query after replacing the MYDBXXXX with your database of interest.

SELECT '; INSERT INTO TBLRECCNT SELECT ' || '''' || TRIM(TABLENAME) || '''' || ', COUNT(*) FROM ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) || ''
FROM DBC.TABLES WHERE TABLEKIND = 'T' AND DATABASENAME = 'MYDBXXXX' ;

-- Step 3. Copy paste the output of the above query (Remember to discard the column headers if you copied that one) and run the query (same session from where you created the volatile table).

-- step 4 get all the details

SELECT * FROM TBLRECCNT;

P.S. This strategy works if your user id has SELECT access to all of the tables in the DB. Another caveat could be a lock by the load utilities on any of the tables in the DB. in that case you will have to remove the select count(*) statement for that table from STEP 3.

There was another way to do from Teradata Admin too, I just don't recall all the steps now though, pretty similar, save there was no need for volatile tables.

Hmm...

Re: Database Statistics

Thanks Joe.

The query returned no rows . We primarily access thes tables through views . For example, if i want to query customer table for client 'xxx' i use 'v_xxx.customer' in my select statement.

I could use a part of your query to see all the tables .

SELECT TRIM(TABLENAME), TABLEKIND FROM DBC.TABLES
WHERE TABLEKIND = 'T' ;

So I could complete step 1
step 2 returned no rows
step 3 returned no rows

there was no error in executing

thanks,
Jeo

Re: Database Statistics

Hi Joe

I figured out mistake- we have to many databases . I ran your query succefully however the tables names are trimmed in step2 . For example: while querying databse xxx, table customer, the query generated is like below:Note - CUSTO instead of Customer. I tried tweakign the query but was unsuccessful. Do you know whats the problems is?

INSERT INTO TBLRECCNT SELECT 'CUSTOMER', COUNT(*) FROM XXX.CUSTO ;

thanks,

JEOMOAN KURIAN

Re: Database Statistics

Hi Joe,

I tried various combinations of the query you sent . As i was learnign our datawarehouse more i understood some of the information i provided were wrong ( Frankly, this is first time i am using a teradata and I was trying to get it work the same way as an Oracle datawarehouse).

We have about 45 databases ! one for each clients . and we have many table for each clients . I modified the query you had given to generate the SQL for all of them and saved inthe temporary table . It had really worked for me .

Thanks a a lot for the help

Jeomoan Kurian