mrstevec

[mrstevec]

Get Row Counts of all Tables in SQL DB (also Azure)

---- ROW COUNTS OF ALL TABLES ----

SELECT

    t.TABLE_SCHEMA + '.' + t.TABLE_NAME as 'TableName'

    ,[RowCount] = SUM(sp.[rows])

FROM INFORMATION_SCHEMA.TABLES t

JOIN sys.partitions sp

    ON sp.object_id = OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME)

WHERE TABLE_TYPE = 'BASE TABLE'

GROUP BY t.TABLE_SCHEMA + '.' + t.TABLE_NAME

ORDER BY t.TABLE_SCHEMA + '.' + t.TABLE_NAME

-----------------------------------

---- ROW COUNTS IN SQL AZURE ----

select 

t.schema_id,t.name as 'TableName'

, s.row_count as 'RowCount'

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 s.index_id = 1

ORDER BY t.schema_id,t.name

---------------------------------