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:

DROP TABLE IF EXISTS #req_tables
;
SELECT TOP (10000)
     schemaname = SCHEMA_NAME([schema_id])
    ,tablename = [name]
INTO #req_tables
FROM sys.tables
WHERE type_desc = 'USER_TABLE'
ORDER BY schemaname,[name]
;
DROP TABLE IF EXISTS #rows_for_tables
;
CREATE TABLE #rows_for_tables (
    schemaname sysname
    ,tablename sysname
    ,no_of_rows INT
    )
;
DECLARE @schema_name sysname
    ,@table_name sysname
;
SET NOCOUNT ON
;
WHILE EXISTS (
        SELECT *
        FROM #req_tables
        )
BEGIN
    SELECT @schema_name = schemaname
        ,@table_name = tablename
    FROM #req_tables
    ;
    INSERT INTO #rows_for_tables (schemaname,tablename)
    VALUES (@schema_name,@table_name)
    ;
    EXEC ('
    update #rows_for_tables
    set no_of_rows = (
        select count(1)
        from ' + @schema_name + '.' + @table_name + '
        )
    where schemaname = ''' + @schema_name + '''
        AND tablename = ''' + @table_name + '''
    ')
    ;
    DELETE
    FROM #req_tables
    WHERE schemaname = @schema_name
        AND tablename = @table_name
END
;
SET NOCOUNT OFF
;
SELECT *
FROM #rows_for_tables
ORDER BY no_of_rows DESC

Output:

Answer 2

I had the same desire, but in Synapse SQL i get the following error with above solutions:

Queries are not supported in the conditions of WHILE statements.

So i built a 3-step solution:

1. step: execute (generate set of sql queries and create temp.table)
2. step: paste the output from first run into specified area
3. step: execute again (if temp.table exíst execute pasted script)

Solution:

IF OBJECT_ID('tempdb..#alltablesowcount') IS NULL  
BEGIN

SELECT 
    'SELECT @numrow=(select count(*) as anz from [' + SCHEMA_NAME(tbl.schema_id) + '].[' + tbl.name + ']);  insert into #alltablesowcount (TName, TRows) values (''[' + SCHEMA_NAME(tbl.schema_id) + '].[' + tbl.name + ']'',@numrow);'
FROM sys.tables AS tbl
order by tbl.schema_id, tbl.name

DECLARE @numrow int
CREATE TABLE #alltablesowcount (
TName NVARCHAR(100),
TRows INT
)

END 
ELSE
BEGIN

-- paste output from first run HERE and run again -----
-- SELECT @numrow=(select count(*) as anz from [dbo].[example]);    insert into #alltablesowcount (TName, TRows) values ('[dbo].[example]',@numrow);


-------------------------------------------------------
select * from #alltablesowcount
END
--drop table #alltablesowcount