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 ON
create 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)
)
AS
BEGIN
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.
