-- 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
/*
reference query
SELECT TABLE_NAME, COLUMN_NAME, IS_NULLABLE, DATA_TYPE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (DATA_TYPE = 'datetime') AND (NOT (COLUMN_DEFAULT IS NULL))
*/
The output looks like this:
| TableName | CountRecentUpdates |
| Exceptions | 0 |
| Results | 232 |
| Links | 0 |
| Results_Unstructured | 0 |
| Debugging | 3290 |
| AppStatus | 0 |
| OutageLog | 0 |
| Events | 1008 |
| StatusLog | 0 |
| Queries | 3 |
