To count all database updates for all tables (that containe a datetime field), just execute this query:
– purpose – see if this database is being updated
– use a set of queries on datetime fields.
– Make the queries dynamic, and pass in the table and column names.
– collate results into a temp table and then display all together
declare @t_name varchar(50)
declare @c_name varchar(50)
declare @query_base varchar(250)
set @query_base = ‘select {QQQTABLE} as [TableName], count(*) as CountRecentUpdates from QQQTABLE where QQQCOLUMN > dateadd(d,-1,getdate() )’
declare @query varchar(500)
declare @TempTableInsert varchar(80)
set @TempTableInsert = ‘ insert into #MyTempTable ([TableName] , [CountRecentUpdates] ) ‘
CREATE TABLE #MyTempTable ([TableName] varchar(50), [CountRecentUpdates] varchar(50) )
declare ci cursor for
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (DATA_TYPE = ‘datetime’) AND (NOT (COLUMN_DEFAULT IS NULL))
open ci
fetch next from ci into
@t_name,
@c_name
WHILE @@FETCH_STATUS = 0
BEGIN
–print @t_name + ‘ ‘ + @c_name
set @query = replace(@query_base,’QQQTABLE’, @t_name)
set @query = replace(@query ,’QQQCOLUMN’, @c_name)
set @query = replace(@query ,’{‘ , char(39) )
set @query = replace(@query ,’}’ , char(39) )
set @query = @TempTableInsert + @query
–print @query
EXECUTE(@query)
– *************************************************************
– RE-LOAD THE CURSOR
fetch next from ci into
@t_name,
@c_name
end
close ci
deallocate ci
select * from #MyTempTable
drop table #MyTempTable
