Find VB.Net keywords in SQL Server Column Names

Continuing my endeavor to create business objects for a DB2 database on an iSeries as well as a SQL Server database I ran into another problem. There are words that are perfectly fine names for database columns that are VB.Net keywords. For example, VARIANT, ERROR, and ERASE can be used as column names without quoting as in this table:

CREATE TABLE AfewVBNETkeywords (
            VARIANT INT
          , ERROR INT
          , ERASE INT
          )

In addition, any word can be used as a column name even if it's a Transact-SQL keyword if you quote it or put it in brackets. This table consists of all the VB.Net keywords, many of which are also SQL Keywords:

CREATE TABLE [AppVBNetKeywordColumns] (
	[ADDHANDLER] [char] (1)  ,
	[ADDRESSOF] [char] (1)  ,
	[ANDALSO] [char] (1)  ,
	[ALIAS] [char] (1)  ,
	[AND] [char] (1)  ,
	[ANSI] [char] (1)  ,
	[AS] [char] (1)  ,
	[ASSEMBLY] [char] (1)  ,
	[AUTO] [char] (1)  ,
	[BOOLEAN] [char] (1)  ,
	[BYREF] [char] (1)  ,
	[BYTE] [char] (1)  ,
	[BYVAL] [char] (1)  ,
	[CALL] [char] (1)  ,
	[CASE] [char] (1)  ,
	[CATCH] [char] (1)  ,
	[CBOOL] [char] (1)  ,
	[CBYTE] [char] (1)  ,
	[CCHAR] [char] (1)  ,
	[CDATE] [char] (1)  ,
	[CDEC] [char] (1)  ,
	[CDBL] [char] (1)  ,
	[CHAR] [char] (1)  ,
	[CINT] [char] (1)  ,
	[CLASS] [char] (1)  ,
	[CLNG] [char] (1)  ,
	[COBJ] [char] (1)  ,
	[CONST] [char] (1)  ,
	[CSHORT] [char] (1)  ,
	[CSNG] [char] (1)  ,
	[CSTR] [char] (1)  ,
	[CTYPE] [char] (1)  ,
	[DATE] [char] (1)  ,
	[DECIMAL] [char] (1)  ,
	[DECLARE] [char] (1)  ,
	[DEFAULT] [char] (1)  ,
	[DELEGATE] [char] (1)  ,
	[DIM] [char] (1)  ,
	[DIRECTCAST] [char] (1)  ,
	[DO] [char] (1)  ,
	[DOUBLE] [char] (1)  ,
	[EACH] [char] (1)  ,
	[ELSE] [char] (1)  ,
	[ELSEIF] [char] (1)  ,
	[END] [char] (1)  ,
	[ENUM] [char] (1)  ,
	[ERASE] [char] (1)  ,
	[ERROR] [char] (1)  ,
	[EVENT] [char] (1)  ,
	[EXIT] [char] (1)  ,
	[FALSE] [char] (1)  ,
	[FINALLY] [char] (1)  ,
	[FOR] [char] (1)  ,
	[FRIEND] [char] (1)  ,
	[FUNCTION] [char] (1)  ,
	[GET] [char] (1)  ,
	[GETTYPE] [char] (1)  ,
	[GOSUB] [char] (1)  ,
	[GOTO] [char] (1)  ,
	[HANDLES] [char] (1)  ,
	[IF] [char] (1)  ,
	[IMPLEMENTS] [char] (1)  ,
	[IMPORTS] [char] (1)  ,
	[IN] [char] (1)  ,
	[INHERITS] [char] (1)  ,
	[INTEGER] [char] (1)  ,
	[INTERFACE] [char] (1)  ,
	[IS] [char] (1)  ,
	[LET] [char] (1)  ,
	[LIB] [char] (1)  ,
	[LIKE] [char] (1)  ,
	[LONG] [char] (1)  ,
	[LOOP] [char] (1)  ,
	[ME] [char] (1)  ,
	[MOD] [char] (1)  ,
	[MODULE] [char] (1)  ,
	[MUSTINHERIT] [char] (1)  ,
	[MUSTOVERRIDE] [char] (1)  ,
	[MYBASE] [char] (1)  ,
	[MYCLASS] [char] (1)  ,
	[NAMESPACE] [char] (1)  ,
	[NEW] [char] (1)  ,
	[NEXT] [char] (1)  ,
	[NOT] [char] (1)  ,
	[NOTHING] [char] (1)  ,
	[NOTINHERITABLE] [char] (1)  ,
	[NOTOVERRIDABLE] [char] (1)  ,
	[OBJECT] [char] (1)  ,
	[ON] [char] (1)  ,
	[OPTION] [char] (1)  ,
	[OPTIONAL] [char] (1)  ,
	[OR] [char] (1)  ,
	[ORELSE] [char] (1)  ,
	[OVERLOADS] [char] (1)  ,
	[OVERRIDABLE] [char] (1)  ,
	[OVERRIDES] [char] (1)  ,
	[PARAMARRAY] [char] (1)  ,
	[PRESERVE] [char] (1)  ,
	[PRIVATE] [char] (1)  ,
	[PROPERTY] [char] (1)  ,
	[PROTECTED] [char] (1)  ,
	[PUBLIC] [char] (1)  ,
	[RAISEEVENT] [char] (1)  ,
	[READONLY] [char] (1)  ,
	[REDIM] [char] (1)  ,
	[REM] [char] (1)  ,
	[REMOVEHANDLER] [char] (1)  ,
	[RESUME] [char] (1)  ,
	[RETURN] [char] (1)  ,
	[SELECT] [char] (1)  ,
	[SET] [char] (1)  ,
	[SHADOWS] [char] (1)  ,
	[SHARED] [char] (1)  ,
	[SHORT] [char] (1)  ,
	[SINGLE] [char] (1)  ,
	[STATIC] [char] (1)  ,
	[STEP] [char] (1)  ,
	[STOP] [char] (1)  ,
	[STRING] [char] (1)  ,
	[STRUCTURE] [char] (1)  ,
	[SUB] [char] (1)  ,
	[SYNCLOCK] [char] (1)  ,
	[THEN] [char] (1)  ,
	[THROW] [char] (1)  ,
	[TO] [char] (1)  ,
	[TRUE] [char] (1)  ,
	[TRY] [char] (1)  ,
	[TYPEOF] [char] (1)  ,
	[UNICODE] [char] (1)  ,
	[UNTIL] [char] (1)  ,
	[VARIANT] [char] (1)  ,
	[WHEN] [char] (1)  ,
	[WHILE] [char] (1)  ,
	[WITH] [char] (1)  ,
	[WITHEVENTS] [char] (1)  ,
	[WRITEONLY] [char] (1)  
)

The problem comes in when you try to make a business object with a property for every column in a table. Properties in VB.Net are declared as this declaration for the property Foo:

    Public Property Foo() As String
        Get
            ' property get code goes here
        End Get
        Set(ByVal Value As String)
            ' property set code goes here
        End Set
    End Property

But you can't use a VB.Net keyword as the property name and if your column name is a VB.Net keyword, such as Variant, you must either change the column name or use a variation on the column name for the property name, for example: PropVariant. Just try and compiling this property:

    Public Property Variant() As String
        Get
            ' property get code goes here
        End Get
        Set(ByVal Value As String)
            ' property set code goes here
        End Set
    End Property

Of course, an ounce of prevention is worth a pound of cure. Any attempt to head off these types of problems before it get's to the point of having to change column names or fix property names in the VB.Net code is worth a little effort.

This week's UDF is part of my effort to head off problems by detecting problems that crop up when creating business objects. A few weeks back Vol 2 Issue #13 was about udf_TBL_ColPotentialProblemsTAB. It identified columns where there are multiple tables that have columns with the same name but with different data types. And Vol 2 Issue #21 was about udf_Tbl_ColNotPortable, a UDF that returns a table of columns that have spaces or certain special characters in their name.

This week's UDF, udf_VBNET_IsKeywordBIT, tells if a string is one of the VB.Net keywords. In future issues it'll be used to locate columns that won't make good names for business object properties. Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE  FUNCTION dbo.udf_VBNET_IsKeywordBIT (
	@sWord as varchar(255)
)	RETURNS BIT -- 1 when @sWord is a VB.NET language keyword.
/*
* Returns 1 when the input word is a VB.Net keyword,
* otherwise 0.
*
* Example:
SELECT CASE WHEN 1=dbo.udf_VBNET_IsKeywordBIT('AND')
			THEN 'Keyword' else 'Not Keyword' END [AND]
     ,  Case WHEN 1=dbo.udf_VBNET_IsKeywordBIT('SNAFU')
			THEN 'Keyword' else 'Not Keyword' END [SNAFU]
SELECT TABLE_NAME, COLUMN_NAME
     FROM INFORMATION_SCHEMA.COLUMNS
     WHERE 0=OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMSShipped')
          and 1=dbo.udf_VBNET_IsKeywordBIT(COLUMN_NAME)
*
* © Copyright 2004 Andrew Novick http://www.NovickSoftware.com
* 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 Newsletter Vol 2 #28
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN 

       -- USE UPPER incase we're on a case sensitive SQL Server
RETURN CASE WHEN UPPER(LTRIM(RTRIM(@sWord))) in ( 
        'ADDHANDLER', 'ADDRESSOF', 'ANDALSO', 'ALIAS'
        ,'AND', 'ANSI', 'AS', 'ASSEMBLY'
        ,'AUTO', 'BOOLEAN', 'BYREF', 'BYTE'
        ,'BYVAL', 'CALL', 'CASE', 'CATCH'
        ,'CBOOL', 'CBYTE', 'CCHAR', 'CDATE'
        ,'CDEC', 'CDBL', 'CHAR', 'CINT'
        ,'CLASS', 'CLNG', 'COBJ', 'CONST'
        ,'CSHORT', 'CSNG', 'CSTR', 'CTYPE'
        ,'DATE', 'DECIMAL', 'DECLARE', 'DEFAULT'
        ,'DELEGATE', 'DIM', 'DIRECTCAST', 'DO'
        ,'DOUBLE', 'EACH', 'ELSE', 'ELSEIF'
        ,'END', 'ENUM', 'ERASE', 'ERROR'
        ,'EVENT', 'EXIT', 'FALSE', 'FINALLY'
        ,'FOR', 'FRIEND', 'FUNCTION', 'GET'
        ,'GETTYPE', 'GOSUB', 'GOTO', 'HANDLES'
        ,'IF', 'IMPLEMENTS', 'IMPORTS', 'IN'
        ,'INHERITS', 'INTEGER', 'INTERFACE', 'IS'
        ,'LET', 'LIB', 'LIKE', 'LONG'
        ,'LOOP', 'ME', 'MOD', 'MODULE'
        ,'MUSTINHERIT', 'MUSTOVERRIDE', 'MYBASE', 'MYCLASS'
        ,'NAMESPACE', 'NEW', 'NEXT', 'NOT'
        ,'NOTHING', 'NOTINHERITABLE', 'NOTOVERRIDABLE', 'OBJECT'
        ,'ON', 'OPTION', 'OPTIONAL', 'OR'
        ,'ORELSE', 'OVERLOADS', 'OVERRIDABLE', 'OVERRIDES'
        ,'PARAMARRAY', 'PRESERVE', 'PRIVATE', 'PROPERTY'
        ,'PROTECTED', 'PUBLIC', 'RAISEEVENT', 'READONLY'
        ,'REDIM', 'REM', 'REMOVEHANDLER', 'RESUME'
        ,'RETURN', 'SELECT', 'SET', 'SHADOWS'
        ,'SHARED', 'SHORT', 'SINGLE', 'STATIC'
        ,'STEP', 'STOP', 'STRING', 'STRUCTURE'
        ,'SUB', 'SYNCLOCK', 'THEN', 'THROW'
        ,'TO', 'TRUE', 'TRY', 'TYPEOF'
        ,'UNICODE', 'UNTIL', 'VARIANT', 'WHEN'
        ,'WHILE', 'WITH', 'WITHEVENTS', 'WRITEONLY' ,'XOR')
    THEN 1 ELSE 0 END
END
GO
 
GRANT EXEC on dbo.udf_VBNET_IsKeywordBIT to PUBLIC
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Let's try this on the ReportServer database than comes with SQL Server's new Reporting Services:

SELECT TABLE_NAME, COLUMN_NAME
     FROM INFORMATION_SCHEMA.COLUMNS
     WHERE 0=OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMSShipped')
          and 1=dbo.udf_VBNET_IsKeywordBIT(COLUMN_NAME)
GO

(Results)
TABLE_NAME    COLUMN_NAME        
------------- -------------------
Catalog       Property

Looks right to me. You couldn't have a property named Property.

And here's a test query:

SELECT CASE WHEN 1=dbo.udf_VBNET_IsKeywordBIT('AND')
			THEN 'Keyword' else 'Not Keyword' END [AND]
     ,  Case WHEN 1=dbo.udf_VBNET_IsKeywordBIT('SNAFU')
			THEN 'Keyword' else 'Not Keyword' END [SNAFU]


(Results)
AND         SNAFU       
----------- ----------- 
Keyword     Not Keyword

Over the next few weeks we'll build on this UDF with other functions that locate problems in creating business objects. See you then.

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