Script To Show Which Tables Are Changed
The following T-SQL query batch will show you which tables was changed since last execute.
Tracking information: (1) changes row count /insert,update,delete/; (2) changes IDENTITY value; (3) changes physical allocations /used pages/; (4) added new tables; (5) dropped tables.
The batch must be initialized before using (at least once executed).
I use this batch to recognize which tables are changed through using an interface of application.
-- ================================ Initialize work tables
if (IsNull((select object_id('tempdb..#old_stats')), 0) = 0)
begin
select cast(0 as int) as [TableId]
, cast('' as sysname) as [TableName]
, cast(0 as bigint) as [RowsCount]
, cast(0 as int) as [RowsCountModified]
, cast(0 as int) as [PagesUsed]
, cast(0 as numeric) as [CurrentIdentity]
into #new_stats
end
GO
-- ================================ Prepare for cycle
if (IsNull((select object_id('tempdb..#old_stats')), 0) != 0)
drop table #old_stats
GO
select * into #old_stats from #new_stats
GO
drop table #new_stats
GO
-- ================================ Getting new statistic
select so.id as [TableId]
, so.Name as [TableName]
, si.rowcnt as [RowsCount]
, si.rowmodctr as [RowsCountModified]
, si.used as [PagesUsed]
, IsNull(Ident_Current(so.Name), -1) as [CurrentIdentity]
into #new_stats
from SysObjects so
left join sysindexes si on si.id = so.id
where so.xtype = 'u'
and si.indid < 2
order by so.name
GO
--
-- ================================ Compare information
--
-- ================================ 1) Changed rows count
select new.TableName as [TableName(Rows)], old.RowsCount as [RowsCount_Old], new.RowsCount as [RowsCount_New]
, (new.RowsCount - old.RowsCount) as [RowsCount: New - Old]
, old.RowsCountModified as [RowsCountModified_Old], new.RowsCountModified as [RowsCountModified_New]
, (new.RowsCountModified - old.RowsCountModified) as [RowsCountModified: New - Old]
from #new_stats new
inner join #old_stats old on old.TableId = new.TableId
where old.RowsCount != new.RowsCount OR old.RowsCountModified != new.RowsCountModified
GO
-- ================================ 2) Changed IDENTITY value
select new.TableName as [TableName(Identity)], old.CurrentIdentity as [CurrentIdentity_Old], new.CurrentIdentity as [CurrentIdentity_New]
, (new.CurrentIdentity - old.CurrentIdentity) as [New - Old]
from #new_stats new
inner join #old_stats old on old.TableId = new.TableId
where old.CurrentIdentity != new.CurrentIdentity
GO
-- ================================ 3) Changed count of using pages
select new.TableName as [tableName(Pages)], old.PagesUsed as [PagesUsed_Old], new.PagesUsed as [PagesUsed_New]
, (new.PagesUsed - old.PagesUsed) as [New - Old]
from #new_stats new
inner join #old_stats old on old.TableId = new.TableId
where old.PagesUsed != new.PagesUsed
GO
-- ================================ 4) Added new tables
select new.TableName as [TableName(Created)], new.RowsCount, new.RowsCountModified, new.PagesUsed, new.CurrentIdentity
from #new_stats new
left join #old_stats old on old.TableId = new.TableId
where old.TableId is Null
GO
-- ================================ 5) Droppped tables
select old.TableName as [TableName(Dropped)], old.RowsCount, old.RowsCountModified, old.PagesUsed, old.CurrentIdentity
from #old_stats old
left join #new_stats new on new.TableId = old.TableId
where new.TableId is Null
GO 
