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(sOBJ.name) + '''' + ' AS [TableName] , COUNT(*) AS [RowCount] FROM ' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK) ' FROM sys.objects AS sOBJ WHERE sOBJ.type = 'U' AND sOBJ.is_ms_shipped = 0x0 ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name ; EXEC sp_executesql @QueryString
The best part of this query is – it shows list of tables along with rowcount like this:
Thanks to www.mssqltips.com! Source: http://www.mssqltips.com/sqlservertip/2537/sql-server-row-count-for-all-tables-in-a-database/
