How to get rowcount of all tables in SQL Server?

Recently while exploring Sitecore DMS feature, came across a situation where I wanted to know rowcount of all tables in Sitecore Analytics database. So like any other developer, I googled it and found many SQL Queries which accomplish this requirement. But I liked one query very much and hence wanted to share it via this post. You simply need to copy and paste this query to your Query Editor in SQL Server, that’s it.

DECLARE @QueryString NVARCHAR(MAX) ;  SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ','')                        + 'SELECT '                        + '''' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))                        + '.' + QUOTENAME( + '''' + ' AS [TableName]                        , COUNT(*) AS [RowCount] FROM '                        + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))                        + '.' + QUOTENAME( + ' WITH (NOLOCK) '  FROM sys.objects AS sOBJ  WHERE        sOBJ.type = 'U'        AND sOBJ.is_ms_shipped = 0x0  ORDER BY SCHEMA_NAME(sOBJ.schema_id), ;  EXEC sp_executesql @QueryString  

The best part of this query is – it shows list of tables along with rowcount like this:

Thanks to! Source:

Leave a Reply