Uncategorized

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!