SQL Azure - List of tables with record count

Recently i was working in Data Sync issue and to check that we want to compare list of tables in Hub DB and Member DB. I found it intresting that many of the sp and xps commonly used till SQL 2008 is not available in SQL Azure.

Issue – How to list all the tables along with number of records in SQL Azure database ?

Resolution :-

You can run query against sys.dm_db_partition_stats dm to find SQL Azure table related information.  Here is a script I created which helped me to find out list of all the user tables and number of records in each of the table on SQL Azure DB. Run this query against the SQL Azure Database.

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


Please share your comments.