Extract all tables names and their rowcounts from SQL linked server tables

I am using the following select statement to get the row count from SQL linked server table.

SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')

MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?

Thank you,

It depends. if you have the right permissions in DB2, you should be able to enumerate the tables.

However, you do realize that the way your query is written, you are pulling the entire table contents across the network from the linked server and then counting the rows. If it we me, I'd do the count(*) on the remote server and only pull the result across the network.

Thank you. Will let me how to do those? I do have permissions on DB2 but need to know the script to pull the names, row counts, and the size and as you suggested, do the count on the server first.

Thanks,

That is a question for a DB2 forum I believe

The following will get you the table names:

select table_name
  from qsys2.tables
 where table_schema='MHDLIB'
   and table_type='BASE TABLE'

Then it should be easy to construct a query to get the Count:

select 'MHSERV0P' as tablename
      ,count(*) as rowcount
  from MHDLIB.MHSERV0P
union all
select 'MHSERV1P' as tablename
      ,count(*) as rowcount
  from MHDLIB.MHSERV1P
.
.
.

Thank you. I modified the firs part to get Table names as:
select table_name,table_schema
from OPENQUERY (CMSPROD, 'Select * From qsys2.tables')
where table_schema='MHDLIB'
and table_type='BASE TABLE'

and it worked and listed 766 Tables with names. To get the rowcount in each table, I added a count() to the above query, but all I get is rowcount = 1 for each listed Table
select table_name,table_schema,count(
) as rowcounts
from OPENQUERY (CMSPROD, 'Select * From qsys2.tables')
where table_schema='MHDLIB'
and table_type='BASE TABLE'
group by table_name, table_schema
order by table_name

With you union all, do I need to add each of those 766 tables on separate union all? I am not clear on how to get the counts

Thank you,

Yes, you need to build a query string of all 766 tables <- do this automatically in your frontend program.
First query get you all tablenames (please don't use "select *" as it will transfer all columns, and you only need table_name).
For each table name you get, you build a string of queries, where you use the union to combine (like I showed you earlier). When done building the query string, pass is (as parameter 2) to the openquery, and you should be good to go.

Maybe you can use this:

with cte0(table_schema,table_name,rn)
  as (select table_schema
            ,table_name
            ,row_number() over(order by table_name) as rn
        from qsys2.tables
       where table_schema='MHDLIB'
     )
    ,cte1(sql,rn)
  as (select cast('select '''||table_name||''' as table_name,count(*) as c from '||table_schema||'.'||table_name
 as clob) as sql
            ,rn
        from cte0
       where rn=1
      union all
      select a.sql||' union all select '''||b.table_name||''',count(*) from '||b.table_schema||'.'||b.table_name as sql
            ,b.rn
        from cte1 as a
             inner join cte0 as b
                     on b.rn=a.rn+1
     )
select sql
  from cte1
 order by rn desc
 fetch first 1 row only
;

This will return one value (sql) that is actually the query built automatically for you.