Validate syntax of CREATE TABLE DDLs using EXPLAIN Command

Question

I have some create table scripts which I need to pre-validate in my application. I can think of two ways:

1. CREATE table using that script and immediately DROP table.
2. Use EXPLAIN command to find syntactical errors without creating it.

I found 2nd way more efficient. So, I validated CREATE TABLE DDLs using Explain command.

Working :

Teradata

Explain <CREATE TABLE DDL>

Oracle

EXPLAIN PLAN FOR <CREATE TABLE DDL>

Not working :

SQL SERVER

Could not find stored procedure 'explain'. SQLState: S00062 ErrorCode: 2812

Netezza

^ found "CREATE" (at char 18) expecting DELETE' orINSERT' or SELECT' orUPDATE' or `WITH'

DB2

Error: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=TABLE;EXPLAIN CREATE ;JOIN, DRIVER=4.14.111 SQLState: 42601 ErrorCode: -104

---

• Is there any other better way to validate Create Table DDLs?

• Is there any generic way to handle this across popular RDBMS?

• If explain is the only available solution, how to perform explain for SQL Server, Netezza and DB2?

Edit:

Here validate means to check syntax (storage size, precision, scale range violations, reserved keywords as table or column names, etc.)

For example, query like -

create table abc (c1 decimal(555,44))

I want to get precision overflow error beforehand.

Answer 1

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."