All Forums Tools
Ted Colman 1 post Joined 09/07
04 Sep 2007
SQL Syntax Checker

Does Teradata offer any tool to check sql syntax before compilation or execution? For example, Microsoft SQL Server has the sql check button to validate that a sql statement's syntax is correct - does Teradata have a similar tool to do this? If not does anyone have a best known method to accomplish this?

Jim Chapman 449 posts Joined 09/04
04 Sep 2007

The method I use is simply to prefix the SQL statement with the word EXPLAIN. This will catch any syntax errors and verify that all the names referenced in the statement are valid.

davidpracy 22 posts Joined 09/05
04 Sep 2007

As Jim suggests use EXPLAIN.EXPLAIN actually p****s the sql so you get both syntax validataion and and understanding of how the opimisiser will interpret / execute the SQL. The F6 key will EXPLAIN a queury in SQL Assistant. I suggest getting into the routine of EXPLAINing all queries.davidpracy

amilkar0417 1 post Joined 04/15
05 Jul 2016

I my experience, using EXPLAIN is not enough. I often need to create scripts that involve several queries in which the first few queries create new objects and the next queries make a reference to the objects created at the beginning. In such case, if I use EXPLAIN, the last queries fail because the new objects don't exist yet. Creating those objects is not an option as I don't always have access to create objects in some databases. It's true that I can create a development environment to test my queries, but that means I have to point the script to different databases (wherever my development environment is) and that involves me editing the script to test it and then having to edit it again for it to point to the right database. Editing the script before sending it to the DBA for him/her to execute is exactly what I don't want to do. I would like to be able to take my final script and test it as it is without having to touch it. Only using EXPLAIN, that is NOT possible. 
Does anybody know any other option to check for syntax errors without having the rights to create objects in the databases being referenced? 

You must sign in to leave a comment.