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:

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:

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