Monday, 31 March 2014

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(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/

No comments:

Post a Comment