Tuesday, November 24, 2009

How to Avoid SQL Reserved Words

Have you ever been querying a database and received a syntax error because one of your column, table or some other object name is clashing with an SQL reserved word? If so, here is an easy way to avoid reserved words during your development cycle. In simple terms it joins a list of reserved words with your catalog columns.

  1. Simply download the ReservedWords.csv from http://richardlees.com.au:8080/dropzone/SQLReservedWords.csv Or if you prefer, copy the words from SQL Server books online and load them into a table.
  2. Load the words into this table
    create table ReservedWords (ReservedWord varchar(128), ReserveWordList varchar(128))
  3. Run the following query

select o.name [Object Name],c.name [Column Name], w.ReserveWordList
from sys.columns c
inner join coreddsdevtemp..ReservedWords w on w.ReservedWord=c.[name]
inner join sys.objects o on c.object_id=o.object_id and o.type NOT IN ('S','IT')
order by 1,2

You will get a list of all the objects that have column names on the SQL 2008, ODBC, and SQL future reserved lists. By the way, you might want to filter out the ODBC words if they are not appropriate for you.

I should add that it isn't fatal to have reserved words in your columns. It just means that you might have to use delimiters around your column names.

No comments: