atwork.blog

news and infos about microsoft, technology, cloud and more

Get tables and rows of a SQL Azure database quickly

...by using a query as here:

Use SQL Server Management Studio (SSMS) or the new and cost free SQL Operations Studio to run one of the following TSQLs in the desired database:

-- SQL Azure - Get statistics (rows) of all tables quickly (options summarized)

-- method 1: use sys.tables
-- https://blogs.msdn.microsoft.com/arunrakwal/2012/04/09/sql-azure-list-of-tables-with-record-count/
select t.name ,s.row_count from sys.tables t
join sys.dm_db_partition_stats s
ON t.object_id = s.object_id
and t.type_desc = 'USER_TABLE'
and t.name not like '%dss%'
and s.index_id = 1


-- method 2: sys.partitions Catalog View
-- https://www.mssqltips.com/sqlservertip/2537/sql-server-row-count-for-all-tables-in-a-database/
SELECT
       QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
       , SUM(sPTN.Rows) AS [RowCount]
FROM
       sys.objects AS sOBJ
       INNER JOIN sys.partitions AS sPTN
             ON sOBJ.object_id = sPTN.object_id
WHERE
       sOBJ.type = 'U'
       AND sOBJ.is_ms_shipped = 0x0
       AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY
       sOBJ.schema_id, sOBJ.name
ORDER BY [RowCount] DESC
GO

-- method 3: sys.dm_db_partition_stats Dynamic Management View (DMV)
-- https://www.mssqltips.com/sqlservertip/2537/sql-server-row-count-for-all-tables-in-a-database/
SELECT
       QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
       , SUM(sdmvPTNS.row_count) AS [RowCount]
FROM
       sys.objects AS sOBJ
       INNER JOIN sys.dm_db_partition_stats AS sdmvPTNS
             ON sOBJ.object_id = sdmvPTNS.object_id
WHERE
       sOBJ.type = 'U'
       AND sOBJ.is_ms_shipped = 0x0
       AND sdmvPTNS.index_id < 2
GROUP BY
       sOBJ.schema_id, sOBJ.name
ORDER BY [RowCount] DESC
GO

As a result, you get a list of all tables with the number of stored rows, similar as here:

image

Thanks to the contributors (see the links per command) for sharing these easy to use methods. Quick and handy!

  • 4nnQZW

    2/19/2018 1:00:56 PM |

    662436 658737Aw, it was a genuinely very good post. In concept I ought to put in writing related to this furthermore - spending time and actual effort to manufacture a exceptional article� but exactly what do I say� I procrastinate alot and no indicates uncover a strategy to go carried out. 268496

Loading