1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
| -- surveillance des bases de données : donne les dernières dates entrées dans des champs de type datetime
CREATE TABLE #TableCount ( table_schema varchar(250), table_name varchar(250), column_name varchar(250), min_date datetime, max_date datetime, card int
PRIMARY KEY ( table_schema , table_name , column_name ) )
declare @sql nvarchar(1500)
DECLARE db_cursor CURSOR FOR
select N'insert into #TableCount select '''+ table_schema +''' table_schema, ''' + table_name + ''' table_name, ''' + column_name + ''' column_name, min('+column_name+') min_date , max('+column_name+') max_date , count(*) card from ['+ table_schema+ '].['+ table_name + ']' REQ
from information_schema.columns
where data_type = 'datetime'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
--print @sql
exec sp_executesql @sql
FETCH NEXT FROM db_cursor INTO @sql
END
CLOSE db_cursor
DEALLOCATE db_cursor
select * from #TableCount order by max_date desc
drop TABLE #TableCount |
Partager