COUNT Number of Records for all the Tables in a Database

Easiest way to get an exact value of Number of Rows for all the tables in a SQL Server database

1. Use DBCC UPDATEUSAGE - this updates the values of rows for each partition in a table.
2. Use undocumented stored procedure sp_msForEachTable and store the result set in a table.

Below is the query to get required output:

USE [DatabaseName]
GO

DECLARE @DynSQL NVARCHAR(255)
SET @DynSQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')'
EXEC(@DynSQL)

IF OBJECT_ID('tempdb..#T','U') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T (TableName nvarchar(500),NumberOfRows int)
GO

INSERT INTO #T
EXEC sp_msForEachTable 'SELECT PARSENAME(''?'', 1) as TableName,COUNT(*) as NumberOfRows FROM ?'
GO 
SELECT * FROM #T ORDER BY NumberOfRows DESC