How to find current database type

We have a single SQL script for execution on multiple types of database. Is it possible to get 开发者_StackOverflowthe type of current database on which SQL script is executing?

Note: We can't use non standard SQL i.e . TSQL etc.

No, there is nothing in ANSI SQL about determining the database vendor.

Even though there are no SQL queries that tell you the database type, there are queries which only work in certain database types and not in others. For example, SELECT version() will tell you the version of the current database if your database is PostgreSQL but it will cause error if you try to run it in Microsoft. Here are other ways to check.

1. Microsoft, MySQL -> SELECT @@version;
2. Oracle -> SELECT * FROM v$version;
3. PostgreSQL -> SELECT version();

In fact, this is how hackers try to grab information of a hidden database.

Here are other differences I found from a blog

No, not within a SQL Statement.

If you happen to use JDBC, you should be able to extract enough information from java.sql.DatabaseMetaData. Have a look at getDatabaseProductName.

I assume that similar functionality is available for other platforms as well.

SELECT *
FROM information_schema.sql_implementation_info
WHERE implementation_info_name LIKE 'DBMS%'
    ;

Works here:

 implementation_info_id | implementation_info_name | integer_value | character_value | comments 
------------------------+--------------------------+---------------+-----------------+----------
 17                     | DBMS NAME                |               | PostgreSQL      | 
 18                     | DBMS VERSION             |               | 09.01.0002      | 
(2 rows)

I cannot tell if the presense of these items is mandatory in the information_schema. I suppose it is.

matrix of information schema support

While there is nothing in ANSI SQL about determining the database vendor, you can try guess and check. This problem is much easier if you have a predetermined list of vendors from which to choose. I recently ran into this problem where I only had an SQL connection to a database. I did know the file extension of the database file, so that made it a restricted list of vendors. I ran various SQL statements to list the table names until one worked. As far as I've seen, getting a list of table names is different for every database vendor.

This method does require human interaction if all you're doing is running an SQL script. Now if only there was a standard way of handling errors or try-catch in ANSI SQL...