Validate syntax of CREATE TABLE DDLs using EXPLAIN Command

There is no generic/standard method that will work in all DBMS.

I expect all popular DBMS to have something similar to EXPLAIN command. Something to return the execution plan instead of running the query itself. Each server will have its own way of doing it.

http://use-the-index-luke.com/sql/explain-plan shows how to do it for few DBMS. A search for <your DBMS name> explain plan command usually gives good results.

• DB2 - EXPLAIN PLAN FOR
• SQLBase - SET PLANONLY ON
• MySQL - EXPLAIN
• Oracle - EXPLAIN PLAN FOR
• PostgreSQL - EXPLAIN
• SQL Server - SET SHOWPLAN_ALL ON
• Teradata - EXPLAIN
• Netezza - EXPLAIN VERBOSE

Another approach is to start a transaction, run your statement and roll back the transaction. Of course, you need to have proper error handling, which again differs between servers. In SQL Server there is TRY ... CATCH.

It is also worth checking if DDL statements in transactions are supported in the chosen DBMS. For example, in MySQL "Some statements cannot be rolled back. In general, these include data definition language (DDL) statements, such as those that create or drop databases, those that create, drop, or alter tables or stored routines."

You should be able to evaluate the correctness of a query in SQL Server using the format only option SET FMTONLY ON. With this option set SQL Server will not actually attempt to create the tables. Using your example, the T-SQL will look as follow:

SET FMTONLY ONcreate table abc (c1 decimal(555,44))SET FMTONLY OFF

Executing the above T-SQL will return error message ‘Column or parameter #1: Specified column precision 555 is greater than the maximum precision of 38.’

You could also create a stored procedure that evaluates the query for you using the method that works the best on the database platform you are using. I am not familiar with Netezza, Teradata & DB2 but I am assuming that they can execute dynamic SQL. Using this method, you simply pass the query you wish to evaluate as a parameter to the stored procedure from your application layer. The following code snippet show how this can be done for SQL Server:

CREATE PROCEDURE ValidateQuerySyntax(    @query NVARCHAR(MAX))ASBEGIN    SET NOCOUNT ON;    DECLARE @validationQuery NVARCHAR(MAX) = 'SET FMTONLY ON; ' + CHAR(13) + @query + ';' +  CHAR(13) + 'SET FMTONLY OFF;';    BEGIN TRY        EXEC (@validationQuery);        -- Return error code 0 if query validation was successful.        SELECT               0 AS ErrorNumber              ,0 AS ErrorSeverity              ,0 AS ErrorState              ,0 AS ErrorLine              ,'Query evaluated successfully' AS ErrorMessage;     END TRY      BEGIN CATCH          -- Return error information if query validation failed.        SELECT               ERROR_NUMBER() AS ErrorNumber              ,ERROR_SEVERITY() AS ErrorSeverity              ,ERROR_STATE() AS ErrorState              ,ERROR_LINE() AS ErrorLine              ,ERROR_MESSAGE() AS ErrorMessage;     END CATCH; END

Queries can be evaluated as follow:

DECLARE @query_1 NVARCHAR(MAX) = 'CREATE TABLE A (        c1 INT)';DECLARE @query_2 NVARCHAR(MAX) = 'CREATE TABLE B (        c1 INT     c2 INT)';DECLARE @query_3 NVARCHAR(MAX) = 'CREATE TABLE B (        c1 INT     ,c2 DECIMAL(555,44))';EXEC dbo.ValidateQuerySyntax @query = @query_1;EXEC dbo.ValidateQuerySyntax @query = @query_2;EXEC dbo.ValidateQuerySyntax @query = @query_3;

The output of the above validation calls is as follow:

-------------------------------------------------------------------------------------------------------------------------------------------------------------ErrorNumber | ErrorSeverity | ErrorState    | ErrorLine | ErrorMessage-------------------------------------------------------------------------------------------------------------------------------------------------------------0           | 0             | 0             | 0         | Query evaluated successfully-------------------------------------------------------------------------------------------------------------------------------------------------------------102         | 15            | 1             | 4         | Incorrect syntax near 'c2'.-------------------------------------------------------------------------------------------------------------------------------------------------------------2750        | 16            | 1             | 1         | Column or parameter #2: Specified column precision 555 is greater than the maximum precision of 38.-------------------------------------------------------------------------------------------------------------------------------------------------------------

Of course this does mean creating the stored procedure that evaluates the query for you first, but it should simplify the validation of your query across the different database platforms.

My suggestion to (at least) your first two concerns:

• Is there any other better way to validate Create Table DDLs?
• Is there any generic way to handle this across popular RDBMS?

would be to use Perl's parsing and database interface capabilities/features (i.e. Perl DBI module) and write a script that validates the SQL via a prepared statement call against your choice(s) of database(s).

The high-level code flow would be:

1. Connect to your database of choice
2. Run your SQL through Perl's prepare() call (e.g. $dbh->prepare('CREATE TABLE emp (emp_name VARCHAR2(30)')
3. Check the output status of the prepare() call

From the A Short Guide to DBI

The prepare call prepares a query to be executed by the database. The argument is any SQL at all. On high-end databases, prepare will send the SQL to the database server, which will compile it. If prepare is successful, it returns a statement handle object which represents the statement; otherwise it returns an undefined value and we abort the program. $dbh->errstr will return the reason for failure, which might be ``Syntax error in SQL''. It gets this reason from the actual database, if possible.

Perl has some other modules that are worth a look at and may be of use, namely:

SQL::Translator

SQL::Statement