any help on sql query for record counts on various tables in a table

Database
Enthusiast

any help on sql query for record counts on various tables in a table

Hi I have bunch of tables collected from a database and tabulated them in a table
Could you please let me know what is best sql or method to get record counts
on each table in the table i created and listed them

thanks
5 REPLIES
Enthusiast

Re: any help on sql query for record counts on various tables in a table

Follow this steps :

step 1: create table table2 (tablename varchar(50),cnt integer)

step 2 :

select k.x from (
Select substr('''',1,1)||a.tablename||substr('''',1,1) c, a.tablename q, ',count(*) from '||q p,'insert into table2 select'||' '||c||p||';' x
From
(
Select Distinct Trim(databasename)||'.'||Trim(tablename) tablename
From dbc.tables
Where tablekind='T') a
) k

step 3: run this result set in one shot.

Here I have taken example of all the database table u can modify u'r query accordingly.

Regards,
Subhash
Enthusiast

Re: any help on sql query for record counts on various tables in a table

Hi Subash:

Thankyou so much for ur wonderful reply. really appreciate that
But my database is teradata
I guess substr is not working on teradata
any improvements in query to work well in teradata?

Thanks
Enthusiast

Re: any help on sql query for record counts on various tables in a table

hi dear

I have designed this in TD itself...........
And substr will work in TD as well

Subhash
Enthusiast

Re: any help on sql query for record counts on various tables in a table

Hi subhash:

I am trying to execute your query
but I am getting 3807 error saying
database does not exist
Enthusiast

Re: any help on sql query for record counts on various tables in a table

depends from which user u are executing..............

might be possible u don't have access on this schema because this is sys schema

dbc is default schema for Teradata....

it should run....

Regards,
Subhash