Question 105356 in Azure Question posted in
The official documentation to get you started can be found here.

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?

Tags:

3

Answers


  1. 0

    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:

    enter image description here

    Login or Signup to reply.
  2. 0

    Here is the code that will do it for you:

    CREATE TABLE #temp_row_count

    (   row_num INT,
        table_name VARCHAR(100),
        row_count INT
    )

INSERT INTO #temp_row_count 
(row_num, table_name)
(SELECT 
    ROW_NUMBER() OVER(ORDER BY SCHEMA_NAME(tbl.schema_id)+'.'+tbl.name ASC),
    SCHEMA_NAME(tbl.schema_id)+'.'+tbl.name
FROM sys.tables AS tbl
WHERE SCHEMA_NAME(tbl.schema_id) = 'dbo')

DECLARE @Counter INT 
DECLARE @Table VARCHAR(100)
SET @Counter = 1

WHILE @Counter <= (SELECT COUNT(*) FROM #temp_row_count)
BEGIN
    SET @Table = (SELECT table_name FROM #temp_row_count WHERE row_num = @Counter)
    EXEC('UPDATE #temp_row_count SET row_count = (SELECT COUNT(*) FROM '+ @Table +') WHERE row_num='''+ @Counter +'''')
    SET @Counter = @Counter + 1
END

SELECT * FROM #temp_row_count
DROP TABLE #temp_row_count
    Login or Signup to reply.
  3. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search