Page Comparison

 table is important as it will contain the list of tables,
their row counts, and the ID which is the main driver for the loop
*/
If Object_ID('tempdb..#Table_of_Tables') Is Not Null Drop Table #Table_of_Tables
Create Table #Table_of_Tables (
	ID Int Identity(1,1) Not Null
	,Table_Name Varchar(200) Not Null
	,Row_Count BigInt Null
)

-- Get the list of tables and insert into the table_of_tables
-- This will not include the system-generated tables; remove the Where clause for all tables
Insert Into #Table_of_Tables
Select
t.[name]
,Null
From sys.tables t
Join sys.schemas s On t.schema_id = s.schema_id
Where s.[name] = Coalesce(@schema, s.[name]) And t.type_desc = 'USER_TABLE'

-- Begin the looping

Declare @counter Int
Declare @EndRow Int  -- By using a parameter, we can negate the need for re-querying on each loop
Declare @tname Varchar(200)
Declare @sql nVarchar(250)

Set @counter = 1
Select @EndRow = Max(ID)+1 From #Table_of_Tables

While @counter < @EndRow
	Begin
	Select @tname = Table_Name From #Table_of_Tables Where ID = @counter

	-- Temp table to hold the row count
	If Object_ID('tempdb..##RCount') Is Not Null Drop Table ##RCount
	
	Select @sql = Replace('Select Count(1) As Row_Count Into ##RCount From @table','@table',@tname)
	Exec sp_ExecuteSQL @sql
		
	Update a
	Set a.Row_Count = (Select Row_Count From ##RCount)
	From #Table_of_Tables a
	Where a.ID = @counter
	
	Set @sql = Null
	If Object_ID('tempdb..##RCount') Is Not Null Drop Table ##RCount

	Set @counter = @counter +1
End;

-- Final clean-up, move data to a temp table and drop the actual table

Select Table_Name, Row_Count From #Table_of_Tables Order By Table_Name