Pascal MESSAGER (freelance)

/* Recherche de trous de date */
/* -------------------------- */
 /*
 copyright Pascal MESSAGER 2008 - DBA SQL Server 
 messager.pascal.pro@wanadoo.fr 06-63-94-56-97
 dba-sqlserver.over-blog.com
 */

 /* Nettoyage */
 /* --------- */

  drop table #date_pref
  drop table #comptage
  drop table ##pivcompt
  drop table #calendrier

  set nocount on
  set QUOTED_IDENTIFIER ON

 /* Liste des tables concernées */
 /* --------------------------- */

  --select * from sys.tables
  select top 1000 o.name, max(rows) rows
  from sysindexes i
  join sysobjects o on o.id = i.id
  where o.name not like 'sys%'
  group by o.name
  order by 2 desc

 /* Sélection de la colonne date */
 /* ---------------------------- */

  select t.name nomtable, c.name nomcolonne, c.column_id, i.index_id, i.name nomindex,  isnull(key_ordinal, 255) ordreindex
  into #date_pref
  from sys.tables t
  join sys.columns c on c.object_id = t.object_id
  join sys.types y on y.system_type_id = c.system_type_id
  left outer join sys.index_columns ic on ic.object_id = t.object_id and ic.column_id = c.column_id
  left outer join sys.indexes i on i.object_id = t.object_id and i.index_id = ic.index_id -- and i.column_id = ic.column_id
  where c.system_type_id in (58,61)
   and t.name not like 'sys%'
--   and t.name not like '% %' -- !!
   and t.name not like '%tampon' -- !!

  order by t.name, ordreindex, c.column_id

  -- Affichage pour contrôle
  /*
  select a.nomtable, A.nomcolonne, min(A.ordreindex) ordreindex
  from #date_pref a
  where A.nomcolonne = (select top 1 nomcolonne from #date_pref B where B.nomtable = A.nomtable order by ordreindex, column_id)
  group by a.nomtable, A.nomcolonne
  order by a.nomtable
  */

 /* Table de comptages */
 /* ------------------ */

  create table #comptage
  (jour smalldatetime
  , nomtable sysname
  , nbenreg int
  )

 /* Requête à générer */
 /* ----------------- */
  /*
  select @req =
    'insert into #comptage select dateadd(dd,0, datediff(dd,0,' + quotename(@nomcolonne) + '))'
  + ', ''' + @nomtable + ''', count(*) from '  + quotename(@nomtable)
  + ' group by dateadd(dd,0, datediff(dd,0,' + quotename(@nomcolonne) + ')) '
  */

 /* Boucle par curseur */
 /* ------------------ */

  declare @nomtable sysname, @nomcolonne sysname, @ordreindex tinyint

  declare @i smallint
  declare @req nvarchar(1000)
  declare @listepivot nvarchar(1000)
  set @i = 0
  set @listepivot = ''
  set @i = 0

  declare cpt_cur cursor   
  for
   select a.nomtable, A.nomcolonne, min(A.ordreindex) ordreindex
   from #date_pref a
   where A.nomcolonne = (select top 1 nomcolonne from #date_pref B where B.nomtable = A.nomtable order by ordreindex, column_id)
   group by a.nomtable, A.nomcolonne
   order by a.nomtable

  open cpt_cur
  fetch next from cpt_cur
  into @nomtable, @nomcolonne, @ordreindex

  WHILE @@FETCH_STATUS = 0
  begin
   -- comptage par group by
   select @req = 'insert into #comptage select dateadd(dd,0, datediff(dd,0,' + quotename(@nomcolonne) + '))'
    + ', ''' + @nomtable + ''', count(*) from '  + quotename(@nomtable)
    + ' group by dateadd(dd,0, datediff(dd,0,' + quotename(@nomcolonne) + ')) '
   print @req
   exec (@req)
   -- Construit la liste pour le pivot
   set @listepivot =  @listepivot + case when @i = 0 then '' else ', ' end + '"' + @nomtable + '"'
   set @i = @i + 1
   -- Suivant
   fetch next from cpt_cur
   into @nomtable, @nomcolonne, @ordreindex
  end
  close cpt_cur
  deallocate cpt_cur

  -- select * from #comptage
  print @listepivot

 /* Pivotage auto */
 /* ------------- */

  -- drop table ##pivcompt
  
  select @req =
  'select jour,
  ' + @listepivot
  + '
   into ##pivcompt from #comptage

   pivot ( sum(#comptage.nbenreg) for nomtable in ( '
  + @listepivot
  + ' 
  )) as p order by jour desc '
  print @req
  exec (@req)

  -- select * from ##pivcompt

 /* Table calendrier */
 /* ---------------- */

  -- drop table #calendrier
  create table #calendrier (jour smalldatetime)
  declare @deb smalldatetime 
  declare @fin smalldatetime 
  select @deb = min(jour), @fin = max(jour)  from #comptage

  declare @d smalldatetime
  set @d = @deb
  while @d <= @fin
  begin
   insert into #calendrier (jour) values ( @d )
   set @d = dateadd(day, 1, @d)
  end

  -- select * from #calendrier

 /* Final avec trous */
 /* ---------------- */
  
  select #calendrier.jour, datepart(dw, #calendrier.jour) joursemaine , ##pivcompt.*
  from #calendrier
  left outer join ##pivcompt on ##pivcompt.jour = #calendrier.jour
  order by #calendrier.jour desc

-- Fin