Check to see if a string is a T-SQL Keyword

Last week's UDF was udf_VBNET_IsKeywordBIT, which is true when a string is one of the VB.Net keywords. I use it to find column names that won't be usable as business object property names when the business objects are built in VB.Net. This let's me head off problems before they become problems.

This week's UDF, udf_SQL2k_IsKeywordBIT, is a similar UDF for SQL Server T-SQL keywords. In the next few weeks it's going to go into a UDF that locates all sorts of problems in column names. But before we get to that we need this week's UDF. Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  FUNCTION dbo.udf_SQL2K_isKeywordBIT (

	@sWord as varchar(255)
)	RETURNS BIT -- 1 when @sWord is a SS2K keyword.
/*
* Returns 1 when the input word is a SQL Server 2000 keyword,
* otherwise 0.
*
* Example:
select 'AND is ' + Case WHEN 1=dbo.udf_SQL2K_isKeywordBIT('ADD')
			THEN 'Keyword' else 'Not Keyword' END
select TABLE_NAME, COLUMN_NAME
     FROM INFORMATION_SCHEMA.COLUMNS
        WHERE 1=dbo.udf_SQL2K_isKeywordBIT(COLUMN_NAME) 
*
* c Copyright 2003 Andrew Novick All rights reserved
* This software is licensed for use by a single developer.  
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
* Published in the T-SQL UDF of the Week Vol 2 #30 7/6/04
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN 
	DECLARE @sWorking varchar(255)
	DECLARE @bitRC BIT
	-- We do UPPER incase we're working in a SQL 
	SET @sWorking = UPPER(LTRIM(RTRIM(@sWord)))
	SET @bitRC = CASE WHEN @sWorking
			 in ( 
'ADD', 'ALL', 'ALTER', 'AND', 'ANY', 'AS', 'ASC', 'AUTHORIZATION'
, 'BACKUP', 'BEGIN', 'BETWEEN', 'BREAK', 'BROWSE', 'BULK', 'BY'
, 'CASCADE', 'CASE', 'CHECK', 'CHECKPOINT', 'CLOSE', 'CLUSTERED'
, 'COALESCE', 'COLLATE', 'COLUMN', 'COMMIT', 'COMPUTE', 'CONSTRAINT'
, 'CONTAINS', 'CONTAINSTABLE', 'CONTINUE', 'CONVERT', 'CREATE'
, 'CROSS', 'CURRENT', 'CURRENT_DATE', 'CURRENT_TIME'
, 'CURRENT_TIMESTAMP', 'CURRENT_USER', 'CURSOR', 'DATABASE', 'DBCC'
, 'DEALLOCATE', 'DECLARE', 'DEFAULT', 'DELETE', 'DENY', 'DESC'
, 'DISK', 'DISTINCT', 'DISTRIBUTED', 'DOUBLE', 'DROP', 'DUMMY'
, 'DUMP', 'ELSE', 'END', 'ERRLVL', 'ESCAPE', 'EXCEPT', 'EXEC'
, 'EXECUTE', 'EXISTS', 'EXIT', 'FETCH', 'FILE', 'FILLFACTOR', 'FOR'
, 'FOREIGN', 'FREETEXT', 'FREETEXTTABLE', 'FROM', 'FULL', 'FUNCTION'
, 'GOTO', 'GRANT', 'GROUP', 'HAVING', 'HOLDLOCK', 'IDENTITY'
, 'IDENTITY_INSERT', 'IDENTITYCOL', 'IF', 'IN', 'INDEX', 'INNER'
, 'INSERT', 'INTERSECT', 'INTO', 'IS', 'JOIN', 'KEY', 'KILL', 'LEFT'
, 'LIKE', 'LINENO', 'LOAD', 'NATIONAL', 'NOCHECK', 'NONCLUSTERED'
, 'NOT', 'NULL', 'NULLIF', 'OF', 'OFF', 'OFFSETS', 'ON', 'OPEN'
, 'OPENDATASOURCE', 'OPENQUERY', 'OPENROWSET', 'OPENXML', 'OPTION'
, 'OR', 'ORDER', 'OUTER', 'OVER', 'PERCENT', 'PLAN', 'PRECISION'
, 'PRIMARY', 'PRINT', 'PROC', 'PROCEDURE', 'PUBLIC', 'RAISERROR'
, 'READ', 'READTEXT', 'RECONFIGURE', 'REFERENCES', 'REPLICATION'
, 'RESTORE', 'RESTRICT', 'RETURN', 'REVOKE', 'RIGHT', 'ROLLBACK'
, 'ROWCOUNT', 'ROWGUIDCOL', 'RULE', 'SAVE', 'SCHEMA', 'SELECT'
, 'SESSION_USER', 'SET', 'SETUSER', 'SHUTDOWN', 'SOME', 'STATISTICS'
, 'SYSTEM_USER', 'TABLE', 'TEXTSIZE', 'THEN', 'TO', 'TOP', 'TRANSACTION'
, 'TRIGGER', 'TRUNCATE', 'TSEQUAL', 'UNION', 'UNIQUE', 'UPDATE'
, 'UPDATETEXT', 'USE', 'USER', 'VALUES', 'VARYING', 'VIEW'
, 'WAITFOR', 'WHEN', 'WHERE', 'WHILE', 'WITH', 'WRITETEXT')
THEN 1 ELSE 0 END
	RETURN @bitRC
END

GO
 
GRANT EXEC on dbo.udf_SQL2K_IsKeywordBIT to PUBLIC
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

To test this out I use an example table created with the following script.

CREATE TABLE [ExampleTableWithKeywordColumnNames] (
	[END] [char] (10) NOT NULL ,
	[VALUES] [char] (10)  NULL ,
	[CROSS] [char] (10)   NULL 
) ON [PRIMARY]
GO

As you can see it's possible to use T-SQL keywords as column names as long as you bracket them. The problem comes in when you try to use them as property names in client side code in a language such as Visual Basic.Net.

We'll use this UDF in a few weeks but for now let's try it out:

select TABLE_NAME, COLUMN_NAME
     FROM INFORMATION_SCHEMA.COLUMNS
        WHERE 1=dbo.udf_SQL2K_isKeywordBIT(COLUMN_NAME) 

GO


(Results)
TABLE_NAME                            COLUMN_NAME        
------------------------------------- -------------------
ExampleTableWithKeywordColumnNames    END
ExampleTableWithKeywordColumnNames    VALUES
ExampleTableWithKeywordColumnNames    CROSS
T2                                    Desc
Tbl2                                  Desc

As you can see the example table shows up as well as a couple of poorly named test tables with columns named Desc, probably for "description".

Do you have a great UDF that you'd like to share?  Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Send them to: UDF@NovickSoftware.com

and they might be published in this newsletter.  I try and respond to every request that I get. 

Thanks, 
Andrew Novick