Q&A for ebook publishers and readers
-- cachebreaker: Any unique number: "So you can break the cache" /* this will output - Oldest database - Newest database - If a database is currently being restored - Number of database grouped by #days old (with earliest date). - Number of database grouped by #days old (with latest date). - Average in minutes between database creation per day group. */ ;with oldest_db as ( select top 1 database_id, name [Description], create_date, datediff(d, create_date, getdate()) [days ago] from sys.databases where database_id > 5 and name not like '%_Temp' order by create_date ), newest_db as ( select top 1 database_id, name, create_date, datediff(d, create_date, getdate()) [days ago] from sys.databases where database_id > 5 and name not like '%_Temp' order by create_date desc ), temp_db as ( select database_id, name, create_date, datediff(d, create_date, getdate()) [days ago] from sys.databases where database_id > 5 and name like '%_Temp' ), range_start_db as ( select min(database_id) [id], concat('# databases ' ,count(*)) [desc], min(create_date) [cd], datediff(d, min(create_date), getdate()) [days ago] from sys.databases where database_id > 5 group by datediff(d, create_date, getdate()) ) , range_end_db as ( select max(database_id) [id], concat('# databases ' ,count(*)) [desc], max(create_date) [cd], datediff(d, min(create_date), getdate()) [days ago] from sys.databases where database_id > 5 group by datediff(d, create_date, getdate()) ), average_db as ( select max(database_id) [id], concat('average ' , datediff(n, min(create_date), max(create_date)) / convert(decimal,count(*))) [desc], null [f], datediff(d, min(create_date), getdate()) [days ago] from sys.databases where database_id > 5 group by datediff(d, create_date, getdate()) ) select 1, 'oldest ' [category], * from oldest_db union select 2, 'newest ',* from newest_db union select 3, 'restoring ',* from temp_db union select 4, 'days group start',* from range_start_db union select 5, 'days group end',* from range_end_db union select 6, 'duration (min)',* from average_db order by 1 ; select min(create_date) [earliest date (UTC)] , max(create_date) [latest date (UTC)] , datediff(d, max(create_date), getdate()) [days ago] , coalesce((select max(name) from sys.databases where name like '%_Temp'), 'None') [restoring] , (select string_agg(status,', ') from ( select concat( count(*), ' refreshed ', datediff(d, create_date, getdate()), ' days ago, took ', datediff(n, min(create_date),max(create_date)), ' minutes' ) [Status] from sys.databases where database_id > 5 group by datediff(d, create_date, getdate()) ) data ) [Status of database updates] from sys.databases where database_id > 5 select string_agg(status,', ') [Status of database updates] from ( select concat( count(*), ' refreshed ', datediff(d, create_date, getdate()), ' days ago, took ', datediff(n, min(create_date),max(create_date)), ' minutes' ) [Status] from sys.databases where database_id > 5 group by datediff(d, create_date, getdate()) ) data select ##cachebreaker?0## from sys.databases where database_id > 5 print 'no cache ##bypassCache?true##'