Efficient SQL test query or validation query that will work across all (or most) databases

by

After a little bit of research along with help from some of the answers here:

SELECT 1

  • H2
  • MySQL
  • Microsoft SQL Server (according to NimChimpsky)
  • PostgreSQL
  • SQLite
  • Hive

SELECT 1 FROM DUAL

  • Oracle

SELECT 1 FROM any_existing_table WHERE 1=0

or

SELECT 1 FROM INFORMATION_SCHEMA.SYSTEM_USERS

or

CALL NOW()

  • HSQLDB (tested with version 1.8.0.10)

    Note: I tried using a WHERE 1=0 clause on the second query, but it didn’t work as a value for Apache Commons DBCP’s validationQuery, since the query doesn’t return any rows

VALUES 1 or SELECT 1 FROM SYSIBM.SYSDUMMY1

SELECT 1 FROM SYSIBM.SYSDUMMY1

  • DB2

select count(*) from systables

  • Informix

More Related Contents:

  1. What happens to an uncommitted transaction when the connection is closed?
  2. What does sp_reset_connection do?
  3. Why isn’t SQL ANSI-92 standard better adopted over ANSI-89?
  4. Generating a range of numbers in MySQL
  5. Truncate (not round) decimal places in SQL Server
  6. Getting list of tables, and fields in each, in a database
  7. Is there something wrong with joins that don’t use the JOIN keyword in SQL or MySQL?
  8. Dynamic Pivot Columns in SQL Server
  9. Is it possible to select sql server data using column ordinal position
  10. What is easier to read in EXISTS subqueries? [closed]
  11. Selecting data into a Postgres array
  12. How to run a stored procedure in sql server every hour?
  13. How do I see active SQL Server connections?
  14. How to pivot rows into columns (custom pivoting)
  15. UPDATE if exists else INSERT in SQL Server 2008 [duplicate]
  16. Calculate the number of records for each date between 2 dates
  17. What is causing Foreign Key Mismatch error?
  18. T-SQL: How to use parameters in dynamic SQL?
  19. MySQL – how to front pad zip code with “0”?
  20. How to compare datetime with only date in SQL Server
  21. SQL server select distinct rows using most recent value only
  22. How can I implement SQL INTERSECT and MINUS operations in MS Access
  23. Split words with a capital letter in sql
  24. Access 2010: Syntax error (missing operator) in query expression
  25. Generate random int value from 3 to 6
  26. scope_identity vs ident_current
  27. SQL INSERT INTO from multiple tables
  28. How to Execute SQL Query without Displaying results
  29. RegEx to Detect SQL Injection
  30. PostgreSQL – dynamic value as table name [duplicate]

Leave a Comment