Validating Object Names Against Reserved Keywords

Question: I know I shouldn't use reserved keywords as table or column names, so what's the best way to validate a word that I want to use as an object name? Can I find out whether a specific word is a keyword programmatically?

Answer: I'm not familiar with a built-in tool in T-SQL that you can use programmatically to check whether a specific word is a keyword, but you can create a user-defined function (UDF) or stored procedure that does just that. You can create a table that stores all the reserved keywords, then instruct the function or stored procedure to check that table. You can download Web Listing 1, the code that creates and populates the Keywords table. Or, you can obtain the list of reserved keywords from SQL Server Books Online (BOL). In SQL Server 2000's BOL, then laboriously generate all the INSERT statements yourself, as I did for Web Listing 1. You need to populate the Keywords table only once (until Microsoft updates the keyword list). Next, create the dbo.fn_iskeyword UDF that Listing 7 shows. Then, use the UDF in the following SELECT statement to determine whether "SELECT" or "Itzik" is a keyword:

SELECT dbo.fn_iskeyword(N'SELECT'), dbo.fn_iskeyword(N'Itzik')

The output returns 1 for "SELECT"—meaning that it's a keyword—and 0 for "Itzik."

—Itzik Ben-Gan