Prendete un datawarehouse abbastanza complesso, con decine di tabelle dei fatti strutturate secondo una modellazione ben precisa:

  • tutte le tabelle (dei fatti) si chiamano “fact_…”
  • tutte le chiavi si chiamano “ID…”
  • (per renderla semplice in questo post) tutti le misure sono definite INT o DECIMAL

Qualcosa come:

image

Adesso prendete la necessità di voler modificare tutte le tabelle per “offuscare” (=modificare) i dati che il vostro cliente ha inviato per test e collaudo.

La domanda è: posso autogenerare delle istruzioni di UPDATE che risolvano il mio problema (senza impazzire e senza perdere tempo)?

Partiamo da un’idea di come generare valori di test per i due tipi di dato a cui abbiamo limitato lo scenario

  • INT
SELECT CAST(RAND(CHECKSUM(NEWID())) * 100000 AS INT)
  • DECIMAL
SELECT Round(CAST(1000 + RAND(CHECKSUM(NEWID())) * 150000 AS decimal(8,2)),2)

A questo punto generiamo le istruzioni da eseguire tramite i metadati recuperati con la vista INFORMATION_SCHEMA.COLUMNS:

SELECT
   
'UPDATE '
      
+ QUOTENAME( table_schema )
       +
'.'
      
+ QUOTENAME( table_name )
       +
' SET '
      
+ column_name
      
+ ' = '
      
+
      
cast
      
(
      
case DATA_TYPE when 'int' then (SELECT CAST(RAND(CHECKSUM(NEWID()))
             *
100000 AS INT))
         
when 'decimal' then (SELECT Round(CAST(1000 + RAND(CHECKSUM(NEWID()))
             *
150000 AS decimal(8,2)),2))
      
end
       as varchar
(30))
 
FROM INFORMATION_SCHEMA.COLUMNS
 
WHERE table_name LIKE 'fact%' AND column_name NOT LIKE 'Id%';

Questo il nostro risultato:

image

A questo punto non ci resta che mandare in esecuzione, una dopo l’altra, le istruzioni appena generate:

SET NOCOUNT ON;
CREATE TABLE #statements( s nvarchar( max ));


INSERT INTO #statements
SELECT 
    'UPDATE ' 
       + QUOTENAME( table_schema ) 
       + '.' 
       + QUOTENAME( table_name ) 
       + ' SET ' 
       + column_name 
       + ' = ' 
       + 
       cast 
       (
       case DATA_TYPE 
          when 'int' then (SELECT CAST(RAND(CHECKSUM(NEWID()))      
             * 100000 AS INT))
          when 'decimal' then (SELECT Round(CAST(1000 + RAND(CHECKSUM(NEWID())) 
             * 150000 AS decimal(8,2)),2))
       end
       as varchar(30)) 
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name LIKE 'fact%' AND column_name NOT LIKE 'Id%';


DECLARE @s nvarchar( max ) = ( SELECT TOP 1 s
                                 FROM #statements );

WHILE @s IS NOT NULL
    BEGIN
        PRINT @s;
        EXEC sp_executesql @s;

        DELETE FROM #statements
          WHERE s = @s;
        SET @s = ( SELECT TOP 1 s
                     FROM #statements );
    END;

DROP TABLE #statements;
 GO