Проверка sql скрипта на ошибки

SQL queries are easy to learn and reuse.

The difficulty of reading and understanding current SQL queries is known as query interpretation. It’s frequently as difficult as Query Composition, which entails writing a new query.

SQL Syntax Checker validates and marks any errors in your SQL queries.

Common Causes of Syntax Errors

• Mismatched number of open and close parentheses
• Improper query language used 
• The data required for the query is missing
• Typos
• Syntax errors such as misspelling
• Use of Reserved words
• An old version of the keyword is used
• Misspelling a keyword or function name

How to Validate Syntax and Disable Statement Execution

Before executing SQL on your production database server, you can run a SQL syntax check without connecting to the database server and look for syntax issues.
The following are supported by the database: Oracle (PLSQL), SQL Server, MySQL, DB2, and Access are all examples of database management systems.
When you’re debugging and come across any syntax that’s part of a long query and wants to validate it, all you have to do is use Syntax.

SQL is the Language used to Communicate with Databases

SQL, SQL Server, MySQL, PostgreSQL, Oracle, and so on. You want to learn SQL, but you’re intimidated by the number of keywords and don’t know where to begin. Let’s go over the definitions for each of these terms.

A database is a type of computer program that stores and processes vast volumes of information. Database vendors come in a variety of shapes and sizes. Database products from different vendors include PostgreSQL, MySQL, Oracle, and SQL Server. The programming language SQL is used to communicate with these databases, and each database product has its SQL variant. These variations are referred to as SQL dialects.

Using SQL Queries to Create Visualizations

Many individuals have tried but failed to create a successful parser due to the intricacy of the SQL grammar and dialicts. Our parser reduces the problems of deciphering SQL grammar. The parsing logic generates an abstract syntax tree (AST) containing «raw» or optionally qualified table and column IDs.

AST Tree Algorithm

The parsing stage entails breaking down the components of a SQL statement into a data structure that may be processed by subsequent algorithms. The database only parses a statement when the application tells it to. Therefore only the application, not the database, may reduce the number of parses. Various utility function analyze the AST tree to identify the components:

1. what tables appear in the query?
2. what columns appear in the query, per clause?
3. what is the table/column lineage of a query?
4. what sets of columns does a query compare for equality?

A SQL syntax tool is a software program which can be used to check and validate the structure and syntax of SQL statements. It can also provide assistance in creating complex SQL queries. With such a tool, users can ensure that their SQL code is efficient and correct before deploying it.

In this article, we will look at the 2 different SQL syntax checker tools that help to find the syntax errors of the queries without executing them.

What is a SQL syntax checker?

SQL syntax checker tools validate SQL syntax or indicate the incorrect syntax errors if it exists. These tools can
be helpful to determine the syntax errors without executing the whole query. The following 2 tools can be used to
validate the syntax of our T-SQL queries:

• SQL Server Management Studio (SSMS)
• SQL Fiddle

What is the query parsing in SQL Server?

When we submit a query in SQL Server to execute, it performs 3 essential phases during the execution of the query:

• Parse: In this phase, the Query Parser checks and validates the syntax of the SQL statement and generates a parse tree of the query. The parse tree is sent to the next stage for processing
• Compile: In this phase, the query optimizer generates an execution plan for the query
• Execute: In this final stage, the storage engine executes the SQL statements

How to validate query syntax with SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) is an advanced integrated development tool to manage, administrate, and
configure SQL Server and it also offers a query editor option to develop and execute T-SQL queries. We can find a Parse button on the query editor toolbar of SSMS, that only checks the syntax of the selected statement or all
statements that are given by the users. So that, we can use SSMS as a SQL syntax checker tool.

Here we need to take into account that, when we parse a query the compile and execute phases are not performed. In the following example, we will check the syntax of a very simple query. To validate a query syntax consists of only 2 simple steps:

• Paste or write query into the query panel
• Click the parse button or press the Control + F5 key combination

As seen, the query syntax has been validated successfully. Now we will remove the FROM clause of the statement and re-parse the query.

After the re-parsing of the query, SQL Server returns an incorrect syntax error. Another option to check the syntax of the queries is using the SET PARSE ONLY command. This command configures the session into parsing mode.

SQL Fiddle

SQL Fiddle is an online web application that can be used to practice or share queries with their schema build script for different database systems.

Besides this, we can use SQL Fiddle as a SQL syntax checker but we need to create all objects that are placed in the query. For example in this sample query, we can build the schema and execute the query.

At the same time, it shows the execution plan of a query after its execution.

How to compile queries with no execute: SET NOEXEC ON command

After enabling the NOEXEC option for a session, SQL Server parses and compiles each statement of the query but it
does not execute the query. The advantage of this command is to perform the parse and compile phases. NOEXEC option
provides the deferred name resolution, so it controls only the referenced if one or more referenced objects in the
batch don’t exist, no error will be thrown. We will explain this concept with a very simple example. In the example
query, everything is okay because the table and columns exist and syntax is also valid

In the following example, the table does not exist but the query is validated but not compiled.

In this last example, SQL Server does not find the referenced objects so it will return an error.

When we only parse the following query the result will return successfully but the syntax of the query is invalid
because of the missing column names after the group by.

Despite that, after enabling the SET NOEXEC option, the query result will return an error.

This example shows the NOEXEC and PARSEONLY option differences. When we correct the misspelling of the syntax, SQL
Server does not return any error.

Another key point about the SET NOEXEC command is related to the cached execution plans. SQL Server stores the
execution plan of the executed queries in the plan cache. When we execute a query after enabling the NOEXEC option
and if this query is not returned any error, the execution plan of the query will be stored in the plan cache. Let’s
look at this working mechanism with an example. Firstly, we will clear the plan cache data of the example query if
it exists. To do this, we will execute the following query and find the plan cache details.

As a second step, we will drop the execution plan that is stored for the sample query. We will pass the plan handle
data as a parameter to the DBCC FREEPROCCACHE.

Before executing the query, we can create an extended event session to observe the query compilation event. This extended
event must include the query_pre_execution_showplan. This event captures the SQL statement is compiled. At the same
time, this event shows the execution plan in an XML format.

As we have explained, after enabling the NOEXEC command the query is compiled by the query optimizer.

Conclusion

In this article, we have looked at two different SQL syntax checker tools to validate our queries without executing
them.

SELECT STATEMENT_DIGEST_TEXT in MySQL 8.0 can be used for MySQL query syntax validation.

8.0.4>SELECT STATEMENT_DIGEST_TEXT('FLUSH TABLES')\G
STATEMENT_DIGEST_TEXT('FLUSH TABLES'): FLUSH TABLES 

8.0.4>SELECT STATEMENT_DIGEST_TEXT("SET GLOBAL second_cache.key_buffer_size=128*1024;")\G
STATEMENT_DIGEST_TEXT("SET GLOBAL second_cache.key_buffer_size=128*1024;"): SET GLOBAL `second_cache` . `key_buffer_size` = ? * ? ;

8.0.4>SELECT STATEMENT_DIGEST_TEXT("create TABLE t1 ( a2 int unsigned not null, b2 int unsigned not null, c2 int unsigned not null, primary key (a2), index b2x (b2), index c2x (c2) ) ENGINE=MEMORY;")\G
STATEMENT_DIGEST_TEXT("create TABLE t1 ( a2 int unsigned not null, b2 int unsigned not null, c2 int unsigned not null, primary key (a2), index b2x (b2), index c2x (c2) ) ENGINE=MEMORY;"): CREATE TABLE `t1` ( `a2` INTEGER UNSIGNED NOT NULL , `b2` INTEGER UNSIGNED NOT NULL , `c2` INTEGER UNSIGNED NOT NULL , PRIMARY KEY ( `a2` ) , INDEX `b2x` ( `b2` ) , INDEX `c2x` ( `c2` ) ) ENGINE = MEMORY ; 

If the SQL is not supported, you’ll get an error. Like the next one, but there is something special about this response;

8.0.4>SELECT STATEMENT_DIGEST_TEXT('HELP SELECT')\G
ERROR 3676 (HY000): Could not parse argument to digest function: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT' at line 1".

Did you see what was special? It is the fact that ‘HELP’ is a valid, but client-side only keyword — i.e. not a server keyword. In any case, an invalid SQL statement will produce a similar situation; an ERROR.

Thus, you can check based on ERROR vs NO ERROR to know whether the passed SQL syntax is valid or not (excluding the very limited set of client-side-only commands, but those would not be of interest to most people).

Summary; SELECT STATEMENT_DIGEST_TEXT is a comprehensive SQL parser (while that may not be it’s direct/intended function) which can be used in all cases to check the validity of statements quickly and without actually executing them. This is huge progress as far as SQL validity validation is concerned.

Note that you need to have a MySQL server up and running for this. You can pass queries using the mysql -e client, or use a pipe to mysql etc.