Get tables and rows of a SQL Azure database quickly

2017-12-21 | Toni Pohl

…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!

Categories: Azure, English, Developer, Microsoft, SQL Server

Source: http://blog.atwork.at/post/2017/12/21/Get-tables-and-rows-of-a-SQL-Azure-database-quickly