Archive for April, 2009

count all recently updated time-sensitive records in an entire database

April 8, 2009


-- 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