# Importa configurazione . "$PSScriptRoot\conf.ps1" $scriptName = $MyInvocation.MyCommand.Name $logFile = Join-Path $env:logDir "${scriptName}.log" "start" | Out-File -FilePath $logFile # Imposta il percorso di output nella cartella sql $OutputPath = Join-Path $env:workdir "sql\tables_from_select" # Verifica che la directory di output esista, altrimenti la crea if (-not (Test-Path -Path $OutputPath)) { New-Item -ItemType Directory -Path $OutputPath | Out-Null } try { # Carica l'assembly System.Data Add-Type -AssemblyName System.Data # Definizione manuale dell'elenco delle tabelle da elaborare # Formato: ogni elemento è un oggetto con proprietà DbName, SchemaName e TableName $tables = @( @{ DbName = "CatalogoProdotti"; SchemaName = "dbo"; TableName = "AnAlberatura" }, @{ DbName = "CatalogoProdotti"; SchemaName = "dbo"; TableName = "AnCompagnia" }, @{ DbName = "CatalogoProdotti"; SchemaName = "dbo"; TableName = "AnEmittente" }, @{ DbName = "CatalogoProdotti"; SchemaName = "dbo"; TableName = "AssetClass" }, @{ DbName = "CatalogoProdotti"; SchemaName = "dbo"; TableName = "CatalogoProdotti" }, @{ DbName = "CatalogoProdotti"; SchemaName = "dbo"; TableName = "CATINAREA" }, @{ DbName = "CatalogoProdotti"; SchemaName = "dbo"; TableName = "Mappatura" }, @{ DbName = "CatalogoProdotti"; SchemaName = "dbo"; TableName = "NeedArea" }, @{ DbName = "CatalogoProdotti"; SchemaName = "dbo"; TableName = "ProdottiNucleo" }, @{ DbName = "CatalogoProdotti"; SchemaName = "dbo"; TableName = "TB_ESG_ANAG_STRUM" }, @{ DbName = "CatalogoProdotti"; SchemaName = "dbo"; TableName = "VDivPtfConCatalogo" }, @{ DbName = "consulenzaEvoluta"; SchemaName = "dbo"; TableName = "Parentela" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "anagrafica_promotori" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "Area" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "AreaModello" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "Cliente" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "ClientePB" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "Cointestatari" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "CointestatariDettaglioDiritto" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "Contratto" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "DComune" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "Delegato" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "dettaglioClientePB" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "DettaglioOperazione" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "DettaglioPosizionePortafoglio" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "DettAltriProdotti" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "DettImmobiliare" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "DettPatrAziendale" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "DettProdottiTerzi" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "dfinalita" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "DistribuzionePtfModello_Tattici" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "Dnazione" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "DNazioneEstera" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "dprofilo" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "dtipoimmobile" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "eventiCliente" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "GradoParentela" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "MappaturaPT" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "param" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "PatrimonioImmobiliareStaging" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "PatrimonioTerzi" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "Pianificazione" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "PiramideModello" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "portafogliomodello" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "PosizioniPortafoglio" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "PosizioniProposta" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "PRAM_NOBPREZ" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "ProgettiPiramideModello" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "Progetto" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "PromotoreBancario" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "Proposta" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "REP_ASSET_1_E_2_ORDINATE" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "REP_S4_S5_S13_TRAD_UNTIL_LIV3" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "REP_S80_EVOL_STAMPA_C6" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "Robo_ProgettoRobo" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "token" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "v_servizi_aggiuntivi" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "VCONTRATTIFINEAVANZATO" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "vinfocliente" }, @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "W6ASSETPERC" }, @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "CaricamentoMassivo" }, @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "CaricamentoMassivoC6SC" }, @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "CBS_DbContrattoBase" }, @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "CBS_DbContrattoSei" }, @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "CBS_DbVariazFreqContrSei" }, @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "CBS_DbVariazStmrepContrSei" }, @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "CBS_HistDatiIngresso" }, @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "CBS_Scarti" }, @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "CBS_STAMPAREPORT" }, @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "CBS_STAMPAREPORT_TEST" }, @{ DbName = "consulenzaUnicafl"; SchemaName = "dbo"; TableName = "ClienteCambioReteContratto" }, @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "Immobiliare_Cliente" }, @{ DbName = "consulenzaUnicafl"; SchemaName = "dbo"; TableName = "Immobiliare_RichiesteMonitoraggioCatastoCliente" }, @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "logBatchGenerale" }, @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "RDR_VALUTE_DB_DTGIORNALIERI" }, @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "RICODIF_CF_PIVA" }, @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "Semaforo" }, @{ DbName = "SEIAlertDW"; SchemaName = "dbo"; TableName = "Vis_Mifid_Report" } ) Write-Host "Trovate $($tables.Count) tabelle" "Trovate $($tables.Count) tabelle" | Out-File -FilePath $logFile -Append # Funzione per formattare il nome del file function Format-FileName { param([string]$name) # Sostituisce caratteri non validi con underscore $invalidChars = [IO.Path]::GetInvalidFileNameChars() $invalidChars += '[', ']', ' ', '(', ')', '{', '}', '´', '`', '''', '"' $result = $name foreach ($char in $invalidChars) { $result = $result.Replace($char, '_') } return $result } # Per ogni tabella foreach ($table in $tables) { $dbName = $table.dbName $schemaName = $table.SchemaName $tableName = $table.TableName $formattedDbName = Format-FileName $dbName $formattedSchemaName = Format-FileName $schemaName $formattedTableName = Format-FileName $tableName Write-Host "Elaboro ${dbName}.${schemaName}.${tableName}..." "Elaboro ${dbName}.${schemaName}.${tableName}..." | Out-File -FilePath $logFile -Append # Query per ottenere la struttura della tabella basata su SELECT * $columnQuery = @" SELECT c.name as ColumnName, t.name as DataType, c.max_length, c.precision, c.scale, c.is_nullable, CASE WHEN t.name IN ('char', 'varchar', 'nchar', 'nvarchar') THEN t.name + '(' + CASE WHEN c.max_length = -1 THEN 'MAX' WHEN t.name LIKE 'n%' THEN CAST(c.max_length/2 AS VARCHAR) ELSE CAST(c.max_length AS VARCHAR) END + ')' WHEN t.name IN ('decimal', 'numeric') THEN t.name + '(' + CAST(c.[precision] AS VARCHAR) + ',' + CAST(c.[scale] AS VARCHAR) + ')' ELSE t.name END as FullDataType FROM [$env:dbHostInput].[$dbName].sys.columns c INNER JOIN [$env:dbHostInput].[$dbName].sys.types t ON c.user_type_id = t.user_type_id INNER JOIN [$env:dbHostInput].[$dbName].sys.tables tbl ON c.object_id = tbl.object_id WHERE SCHEMA_NAME(tbl.schema_id) = '$schemaName' AND tbl.name = '$tableName' ORDER BY c.column_id "@ # Ottieni la struttura delle colonne $connectionString = "Server=$($env:dbHostInput);Database=$($env:dbNameInput);User Id=$($env:dbUserInput);Password=$($env:dbPasswordInput);TrustServerCertificate=True" $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString) $command = New-Object System.Data.SqlClient.SqlCommand($columnQuery, $connection) $adapter = New-Object System.Data.SqlClient.SqlDataAdapter($command) $dataset = New-Object System.Data.DataSet $adapter.Fill($dataset) | Out-Null $columns = $dataset.Tables[0] # Costruisci la CREATE TABLE $createTableScript = "CREATE TABLE [$dbName].[$schemaName].[$tableName] (`n" $columnDefinitions = @() foreach ($column in $columns) { $nullability = if ($column.is_nullable) { "NULL" } else { "NOT NULL" } $columnDefinitions += " [$($column.ColumnName)] $($column.FullDataType) $nullability" } $createTableScript += $columnDefinitions -join ",`n" $createTableScript += "`n)" # Crea il nome del file $fileName = Join-Path $OutputPath "${formattedDbName}_${formattedSchemaName}_${formattedTableName}.sql" # Aggiungi la SELECT dopo la CREATE TABLE $content = @" $createTableScript; GO -- Popolamento tabella -- INSERT INTO [$dbName].[$schemaName].[$tableName] -- SELECT * FROM [$env:dbHostInput].[$dbName].[$schemaName].[$tableName]; -- GO "@ try { # Scrivi il contenuto nel file con codifica UTF8 con BOM $utf8WithBom = New-Object System.Text.UTF8Encoding($true) [System.IO.File]::WriteAllText($fileName, $content, $utf8WithBom) Write-Host "Creato file per tabella: ${dbName}.${schemaName}.${tableName}" -ForegroundColor Green "Creato file per tabella: ${dbName}.${schemaName}.${tableName}" | Out-File -FilePath $logFile -Append } catch { $errorMessage = "ERRORE durante la scrittura del file per ${dbName}.${schemaName}.${tableName}: $($_.Exception.Message)" Write-Host $errorMessage -ForegroundColor Red $errorMessage | Out-File -FilePath $logFile -Append } } Write-Host "`nGenerazione script completata con successo!" "Generazione script completata con successo!" | Out-File -FilePath $logFile -Append } catch { $errorMessage = "Errore durante l'elaborazione: $_" Write-Host $errorMessage -ForegroundColor Red $errorMessage | Out-File -FilePath $logFile -Append exit 1 }