How to find if a column name is a reserved keyword across various databases

Question

We have a legacy schema file which has the following column names, would like to understand if any of them would cause an issue if we port our application to h2, mysql, postgres or oracle.

e.g how would I know if using this would be ok across the databases listed below. Are there any good tools which can do a preliminary check on this?

key
when
size
type
count
from
with

Answer 1

Should be quite easy to get the list of reserved words and build a table of the reserved words. Here are some lists of reserved words:

• Oracle: http://download.oracle.com/docs/cd/B19306_01/em.102/b40103/app_oracle_reserved_words.htm
• SQL Server:
  http://msdn.microsoft.com/en-us/library/ms189822.aspx
• PostgreSQL:
  http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html
• MySQL:
  https://dev.mysql.com/doc/refman/5.6/en/keywords.html#keywords-in-current-series

And here's an online checker: http://www.petefreitag.com/tools/sql_reserved_words_checker/?word=on

Answer 2

I am not aware of any tables but it should not be difficult to filter them if you have the list of table names and column names.

Oracle has a V$RESERVED_WORDS view in its data dictionary.

All you have to do is to match your table/column names against this: Just add them to temp table and join with tis view. If you have a result for your query then you have a reserved word.

Other databases may have such metadata as well.

Answer 3

DatabaseMetaData.getSQLKeywords() is supposed to return a comma-separated list of reserved words within this database. This list doesn't contain ANSI SQL keywords such as FROM however. I'm not completely sure if this really contains all keywords in all databases however.

For H2, the list of keywords is documented under Keywords / Reserved Words.

Answer 4

The easiest way to find out would be to try it. All of the database management systems you mention are open source or free to download and try. Run your SQL script with the CREATE TABLE statements through them and see what happens.

The problem with asking this without reference to an actual SQL script is that some parsers have various classes of reserved words. Some key words might be listed as key words, but might still be OK to use as column names, but perhaps not in tricky SELECT statement later on. So it's always best to try it out.

I suggest based on the list you give, it won't work in most SQL systems. But you can always consistently double quote the identifiers to steer clear of key word problems. (You will need to run MySQL in ANSI mode, though.)