How to count rows for all existing tables in Azure Synapse SQL Pool?

Question

I'd like to generate a table that consists of list of all available tables and number of rows for each of them.

Table Name	Number of rows
SCHEMA.TABLE_NAME1	30
SCHEMA.TABLE_NAME2	40

I've used the query below:

select schema_name(tab.schema_id) + '.' + tab.name as [table],
sum(part.rows) as [rows]
   from sys.tables as tab
        inner join sys.partitions as part
            on tab.object_id = part.object_id
where part.index_id IN (1, 0) -- 0 - table without PK, 1 table with PK
group by schema_name(tab.schema_id) + '.' + tab.name
order by sum(part.rows) desc

but unfortunately, the number of rows for all tables with more than 1000 rows is 1000 for some reason.

Is there any other way to do that?

Answer 1

The query that you are using is also giving incorrect results for me. For some reason, the rows column in sys.partitions has its value as 1000 when the table is created (even though the tables are empty).

Is there any other way to do that?

• As an alternative, I tried using the following query. I created a temporary table called #rows_for_tables which will be used to store our result.

• Then I have used while loop, where we loop through table names collected in a temporary table req_tables, to calculate the number of rows for each table available in sys.tables.

• We get a table_name, insert it in rows_for_tables, update the no_of_rows field with count of table_name for that particular table and delete that table_name from #req_tables (Since its is inserted and we don't need it).

• Once the above procedure is executed, the required results will be present in #rows_for_tables temporary table.

Total Query:

CREATE TABLE #rows_for_tables
(
    table_name varchar(50),
    no_of_rows int
)

select top(1000) name into #req_tables from sys.tables 

declare @t_name varchar(50) 

while exists (select * from #req_tables) 
begin

    select @t_name = (select top 1 name
                       from #req_tables
                       order by name asc)

    insert into #rows_for_tables(table_name) values(@t_name)

    exec('update #rows_for_tables set no_of_rows = (select count(*) from '+@t_name+') where table_name='''+@t_name+'''')

    delete from #req_tables
    where name = @t_name

end

select * from #rows_for_tables

Output: