SQL query to understand the names of the tables , number of records in a table and size of the table

Database

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

2 REPLIES

Re: SQL query to understand the names of the tables , number of records in a table and size of the table

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

Re: SQL query to understand the names of the tables , number of records in a table and size of the table

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;