CODEWITHSUNDEEP
powershell
avk
avk

Reputation: 61

Powershell Read the table name and use it in a line

Hi i am new to powershell and i have a scenario where i have a script to read all the CREATE TABLES from a sql file. Before these CREATE TABLES statement i have to print a IF EXIST statement which include the table name of the CREATE TABLE statement

Upvotes: 0

Views: 230

Answers (2)

Dave Sexton
Dave Sexton

Reputation: 11188

Just uses a regex replace, something like this:

$x = Get-Content my_file.sql -raw
$r = @'
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'$1')
    DROP TABLE [dbo].[$1]
    --GO

    CREATE TABLE [dbo].[$1]
'@
$x -replace 'CREATE TABLE \[dbo\]\.\[([^\]]+)\]', $r

Upvotes: 1

Chris Dent
Chris Dent

Reputation: 4250

If $SqlStatements is a variable holding the content then this works.

$ifExists = @'
    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{0}')
    DROP TABLE {1}
    --GO
'@

[Regex]::Matches($SqlStatements, '\s*CREATE TABLE (\S+\.\[(\S+)\])') | Sort-Object Index -Descending | ForEach-Object {
    $SqlStatements = $SqlStatements.Insert(
        $_.Index,
        "`r`n" + ($ifExists -f $_.Groups[2].Value, $_.Groups[1].Value) + "`r`n"
    )
}
$SqlStatements

Replacements are done from the end tracking backwards. Attempting to go forwards will invalidate the Index value after the first insert.

Upvotes: 0

Related Questions