Takes the list of tables with missing audit column descriptions then adds it on

AddMissingDescriptionOnAuditColumns.ps1

$file = "C:\temp\audit\staging.csv"
$csv = Import-Csv $file

$ssdtDir = 'C:\GIT\BusinessIntelligence\BusinessIntelligence\1_SQL_Staging\SRC\'


ForEach ($line in $csv) {
    $sqlFilePath = $ssdtDir + $line.SchemaName  + '\Tables\' + $line.TableName + '.sql'
    Write-Host $sqlFilePath

$auditColDesc = @"

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Audit Column - ETL execution ID' , @level0type=N'SCHEMA',@level0name=N'{{SchemaName}}', @level1type=N'TABLE',@level1name=N'{{TableName}}', @level2type=N'COLUMN',@level2name=N'ETL_Run_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Audit Column - ETL execution username' , @level0type=N'SCHEMA',@level0name=N'{{SchemaName}}', @level1type=N'TABLE',@level1name=N'{{TableName}}', @level2type=N'COLUMN',@level2name=N'ETL_Created_By'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Audit Column - ETL execution date time' , @level0type=N'SCHEMA',@level0name=N'{{SchemaName}}', @level1type=N'TABLE',@level1name=N'{{TableName}}', @level2type=N'COLUMN',@level2name=N'ETL_Created_Date'
GO
"@
$auditColDesc = $auditColDesc -replace "{{SchemaName}}", $line.SchemaName
$auditColDesc = $auditColDesc -replace "{{TableName}}", $line.TableName
    a
    $sqlFileContent = Get-Content $sqlFilePath
    $sqlFileContent = $sqlFileContent + $auditColDesc

    Set-Content $sqlFilePath $sqlFileContent
}