sql server tables growth

step 1 : create a table using script below.

create table dbo.all_tbl_size (name varchar(256),rows int,reserved varchar(90),data varchar(90),index_size varchar(90),unused varchar(90),delta_date datetime default getdate()) select * from all_tbl_size

step 2 : execute the below statement to capture the record count of all the tables

EXECUTE sp_MSforeachtable 'insert into dbo.all_tbl_size (NAME,ROWS,RESERVED,DATA,INDEX_SIZE,UNUSED) EXECUTE sp_spaceused [?]'

select * from all_tbl_size  where name <> 'all_tbl_size'
output :

schedule the script in step:2 as a job runs every day or as per your requirement
to calculate the percentage of table growth , use the below query

set transaction isolation level read uncommitted; with cte as ( select ROW_NUMBER () over (partition by name order by delta_date asc) as iid ,  name , rows,delta_date from all_tbl_size ) --select A.iid , B.IID AS B_IID,a.name,a.delta_date,b.delta_date AS B_delta_date,a.rows - b.rows as growth , a.rows , b.rows AS B_ROWS select a.name,a.delta_date ,a.rows ,a.rows - b.rows as growth ,convert(decimal(18,2),( (a.rows - b.rows)/(1.0*a.rows) * 100)) as growth_in_percentage ,STATS_DATE(s.object_id, s.stats_id) as stat_dt from cte a join cte b on a.name = b.name and a.iid - b.iid = 1 left outer join sys.stats s on object_id(b.name) = s.object_id where a.rows - b.rows  >0 and a.name <> 'all_tbl_size'

note : the column stats_dt will be helpful to update the statistics.
output :

If you have multiple tables with same name , but on different schema then you may use the below steps to get the object_id instead table name

1. http://www.calsql.com/2009/01/spspaceused-with-objectid-as-output.html
2. create staging table using below script

create table dbo.all_tbl_size (obj_id int ,rows int,reserved varchar(90),data varchar(90),index_size varchar(90),unused varchar(90),delta_date datetime default getdate())

3.Get the table sizes from all the tables in the database

EXECUTE sp_MSforeachtable 'insert into dbo.all_tbl_size (obj_id ,ROWS,RESERVED,DATA,INDEX_SIZE,UNUSED) EXECUTE sp_spaceused2 [?]'