Powershell script to validate sql syntax errors

Question

Is there any powershell syntax that detects sql syntax errors? For e.g. stored procedures , SQL query , functions etc.

You are looking for an Online SQL compiler like rextester.com/l/sql_server_online_compiler — Vivek Kumar Singh
– Vivek Kumar Singh, Commented Mar 22, 2019 at 7:52
Yes . need a syntax validator written in powershell that will validate syntax errors. — Asi
– Asi, Commented Mar 22, 2019 at 8:10
Which RDBMS are you using? For SQL Server solution, see earlier a question. — vonPryz
– vonPryz, Commented Mar 22, 2019 at 9:41
You'd need to write an SQL parser for that (regardless of what DBMS you're using), which is clearly way beyond the scope of an SO question. — Ansgar Wiechers
– Ansgar Wiechers, Commented Mar 22, 2019 at 10:12

Dylan Watson · Accepted Answer · 2019-06-06 09:00:58Z

2

You can wrap your query with PARSEONLY or NOEXEC, here's an example :

$Server = "SERVER"
$Database = "Database"
$UserId = "USERID"
$Password = "PASSWORD"
$QueryToTest= "SELECT * FROM NO_TABLE !!"

Function Check-Query-Syntax
{
    Param(
        [Parameter(Mandatory = $true)][string]$query
    )
    try
    {
        $sb = New-Object -TypeName "System.Text.StringBuilder"
        [void]$classpath.AppendLine("SET NOEXEC ON;")
        [void]$classpath.AppendLine($query)
        [void]$classpath.AppendLine("SET NOEXEC OFF;")
        $Connection = New-Object System.Data.SQLClient.SQLConnection
        $Connection.ConnectionString = "server='$Server';database='$Database';User Id='$UserId';Password='$Password';trusted_connection=true;"
        $Connection.Open()
        $Command = New-Object System.Data.SQLClient.SQLCommand
        $Command.Connection = $Connection
        $Command.CommandText = $SQLQuery
        $Reader = $Command.ExecuteNonQuery()
        $Connection.Close()
    }
    catch
    {
        Write-Host "Some error"
        throw
    }
}

Check-Query-Syntax $QueryToTest

PARSEONLY NOEXEC

Reference : https://stackoverflow.com/a/3276146/3759822

UPDATE :

If you want to check the queries inside .sql files then execute the script from the directory that contains the scripts

Get-ChildItem .\*.sql | Select -ExpandProperty FullName | ForEach { $query = (Get-Content -Path $_); Check-Query-Syntax $str }

edited Jun 6, 2019 at 9:00

Dylan Watson

2,3232 gold badges20 silver badges38 bronze badges

answered Mar 22, 2019 at 10:17

Fourat

2,4476 gold badges41 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Asi Over a year ago

Hi appreciate your efforts. Basically m trying to check the errors not through sql servers or anything. I have the sql scripts in my folder . I need it to check each file within the folder.

Asi Over a year ago

Yes .. thank you for that . Is it not possible without passing the database details etc? Is there any namespace available that will work in a way to display the errors?

Fourat Over a year ago

@aishwaryamurali you need a connection to a SQL Server database server in order to validate a query because it's done on server side. Whats do you mean by "Is there any namespace available that will work in a way to display the errors?" ?

Collectives™ on Stack Overflow

Powershell script to validate sql syntax errors

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related