Create Procedure sp_All_Table_Row_Count (@schema Varchar(100)) As
/*
Create the holding table:
This 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
'[' + s.[name] + '].[' + 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
-- Fetch the table name
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
-- Use the Replace() function on the script to insert the table name into the code
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;
-- Once Final clean-up, move data to a temp table and drop the actual tablethe loop has completed, just read the entire contents of the #Table_of_Tables
Select Table_Name, Row_Count From #Table_of_Tables Order By Table_Name |