DECLARE @TableFolder VARCHAR(256) = 'C:\Users\gvarol\AppData\Local\Red Gate\SQL Source Control 2\WorkingBases\bgltwkuy.3ox\Tables\' ;
WITH f1
AS (SELECT @TableFolder + di.NAME AS FileName,
Util.dbo.StringConcat(nl.NewLine, '
') AS SQL
FROM Util.FS.GetDirectoryInfo(@TableFolder, '*.sql') di
CROSS APPLY (SELECT Util.FS.ReadAllTextFromFile (@TableFolder + di.name) AS Contents) ct
OUTER APPLY (SELECT TOP 1
Field
FROM Util.dbo.ParseDelimited(Contents, '
') PF
WHERE CHARINDEX('CONSTRAINT DataFolder-', Field) > 0) b
CROSS APPLY (SELECT Field,
CHARINDEX('CONSTRAINT DataFolder-', Field) AS ci
FROM Util.dbo.ParseDelimited(Contents, '
') P2) b2
CROSS APPLY (SELECT CASE WHEN b2.CI > 0 THEN REPLACE(b2.Field, 'CONSTRAINT DataFolder-', 'CONSTRAINT [DataFolder-') + ']'
ELSE b2.Field
END AS NewLine) nl
WHERE b.Field IS NOT NULL
GROUP BY di.NAME)
SELECT *
FROM f1
CROSS APPLY Util.FS.AppendAllTextToFile(FileName, SQL, 1) fs
GO
/* REGEX
dir *.sql | %{
$fileName = $_.Name
$fileNameTemp = [System.IO.Path]::ChangeExtension($_.Name,"tmp");
Get-Content ..\$fileName |
%{$_ -creplace ' \bCONSTRAINT\s(\w+-(?:\w|-|\$)+)\b', ' CONSTRAINT [$1]'} |
Set-Content ..\$fileNameTemp;
del ..\$fileName;
move ..\$fileNameTemp ..\$fileName;
}
*/