azurecountazure-synapse

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


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?


Solution

  • 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?

    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:

    enter image description here