All Forums Database
krishnaprasadmt 2 posts Joined 01/12
28 Aug 2014
SQL query to understand the names of the tables , number of records in a table and size of the table

Hi
Please help me with a  SQL query to understand the names of the tables , number of records in a table and size of the table ?
 
Regards,
Prasad

krishaneesh 140 posts Joined 04/13
28 Aug 2014

select * from dbc.tables;
--list of tables and associated other details across the database
select count(*) from databasename.tablename;
--gives the records for a table in a database.
select databasename,tablename,sum(currentperm) from dbc.tablesize group by 1,2
--gives the size of each table in each database

Raja_KT 1246 posts Joined 07/09
28 Aug 2014

If it is tedious to do one by one , you can think of a script thus:

SELECT 'SELECT '''||TRIM(TABLENAME)||''' AS TABLE_NM, count(*) AS ROW_CNT FROM '||TRIM(DATABASENAME)||'.'||TRIM(TABLENAME)||' GROUP BY 1 UNION all'

FROM DBC.TABLESX

WHERE DATABASENAME = 'XYZ'

 

Take the resultset and you can remove the last UNION ALL

 

 

size of table

sel

databasename,

tablename,

sum (currentperm)/(1024) Space,

from

dbc.tablesize

where

databasename = 'XYZ'

group by 1,2;

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.