Find Columns that don't have Good Property Names

The past two issues have created two UDFs that check a string against the list of keywords in Visual Basic .Net and in SQL Server 2000. The reason that I want to know if a string is a keyword is that I'm frequently build business objects for the applications that I build. The business objects have property names identical to the column name that holds the data. There's no problem using either a VB.Net or T-SQL keyword as a column name, although you might have to bracket the column name in all your SQL script.

Two problems arise, however. Issue #21, a few weeks ago, dealt with using the same column names on DB2 tables. DB2 doesn't like brackets around column names. Without brackets column names with spaces won't work. The other problems comes up when trying to use one of these names as a property name in a VB.Net program. VB.Net just doesn't let you use most keywords as property names. For example, Variant, Date, and Error are unacceptable to the compiler.

So what I've been doing is building up to this weeks UDF, udf_Tbl_ColNameIssueTAB. It pulls together information from several UDFs to show me the name of columns that might prove to be trouble. I run this UDF after the tables have been created but before generating the business objects. This gives me a chance to make corrections before that becomes difficult.

I call this week's UDF udf_Tbl_ColNameIssueTab. It brings together earlier UDFs and you'll need to have them in your database before you can create this one. So if you're about to copy udf_Tbl_ColNameIssueTAB, be sure you get these UDFs which are listed with the issue that published them:

Here's the CREATE FUNCTION script for this week's UDF:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  FUNCTION dbo.udf_Tbl_ColNameIssueTAB (

) RETURNS TABLE
/*
* Returns a table listing columns where there is an issue
* that would prevent either portablity to DB2 or use of the
* column name as a business object property name in a 
* Visual Basic .Net project.
*
* Example:
SELECT * FROM dbo.udf_Tbl_ColNameIssueTAB()
*      
* © 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 as T-SQL UDF of Week Newsletter Vol 2 #30 06/13/04 
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
***************************************************************/

AS RETURN

SELECT TOP 100 PERCENT WITH TIES
       K.Table_NAME
     , K.COLUMN_NAME
     , CASE WHEN 1=dbo.udf_Txt_HasCharInGroup (K.COLUMN_NAME
                                , '$#%^*+/\[]{}()&|<>,.@!|"''=')
            THEN 'Y' ELSE 'N' END [VBNET_Name]
     , case WHEN 1=dbo.udf_VBNET_isKeywordBIT(K.COLUMN_NAME)
            THEN 'Y' ELSE 'N' END [VBNET_Keyword] 
     , case WHEN 1=dbo.udf_SQL2K_isKeywordBIT(K.COLUMN_NAME)
            THEN 'Y' ELSE 'N' END [SQL2K] 
     , CASE WHEN P.TABLE_NAME IS NOT NULL 
            THEN 'Y' ELSE 'N' END [DB2_Col_Name]    
     , CASE WHEN K.TABLE_TYPE = 'BASE TABLE' 
            THEN 'T' ELSE 'V' END [TorV]
    FROM (select C.TABLE_NAME
               , C.COLUMN_NAME
               , T.TABLE_TYPE 
             FROM INFORMATION_SCHEMA.[COLUMNS] C
                INNER JOIN INFORMATION_SCHEMA.[TABLES] T
                    ON C.TABLE_NAME = T.TABLE_NAME
            WHERE ( OBJECTPROPERTY (OBJECT_ID(C.TABLE_NAME)
                                 , 'IsMSShipped') IS NULL
                       OR 0=OBJECTPROPERTY (OBJECT_ID(C.TABLE_NAME)
                                , 'IsMSShipped') 
                  )
               AND(     1=dbo.udf_Txt_HasCharInGroup (C.COLUMN_NAME
                                   , '$#%^*+/\[]{}()&|<>,.@!|"''=')
                     OR 1=dbo.udf_VBNET_isKeywordBIT(C.COLUMN_NAME)
                     OR 1=dbo.udf_SQL2K_isKeywordBIT(C.COLUMN_NAME)
                  )
           GROUP BY C.TABLE_NAME, C.COLUMN_NAME
                   , T.TABLE_NAME, T.TABLE_TYPE
        ) K
       LEFT OUTER JOIN ( SELECT TABLE_NAME, COLUMN_NAME
                            FROM dbo.udf_Tbl_ColNotPortable() ) P
            ON K.TABLE_NAME = P.TABLE_NAME
                AND K.COLUMN_NAME=P.COLUMN_NAME
    ORDER BY K.TABLE_NAME, K.COLUMN_NAME

GO

GRANT SELECT on dbo.udf_Tbl_ColNameIssueTAB to PUBLIC
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

To test this out I use an example table. I created one recently but this one has a few more problems.

CREATE TABLE [ExampleTableWithColumnNameIssues] (
	[Variant] [char] (10),
	[Error] [char] (10) ,
	[Col Name With Spaces] [char] (10)  ,
	[Date] [char] (10),
	[SELECT] [char] (10),
	[Income$] [money] NULL ,
	[Braces{}] [char] (10) 
) 
GO

As you can see it's possible to use T-SQL keywords as column names as long as you bracket them. You can also use the dollar sign and other special characters. 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.

So now let's try out our UDF:

SELECT Table_Name, Column_Name [Col]
       , VBNet_Name [VB NM]
       , VBNet_Keyword [VB Key]
       , SQL2K [SQL]
       , DB2_COl_Name [DB2]
       , [TorV]
   FROM dbo.udf_Tbl_ColNameIssueTAB()
GO

(Results)
Table_Name                         Col     VB NM VB Key SQL DB2 TorV
---------------------------------- ------- ----- ------ --- --- ----
ExampleTableWithColumnNameIssues   Date    N     Y      N   N   T
ExampleTableWithColumnNameIssues   Error   N     Y      N   N   T
ExampleTableWithColumnNameIssues   Income$ Y     N      N   N   T
ExampleTableWithColumnNameIssues   SELECT  N     Y      Y   N   T
ExampleTableWithColumnNameIssues   Variant N     Y      N   N   T
ExampleTableWithKeywordColumnNames CROSS   N     N      Y   N   T
ExampleTableWithKeywordColumnNames END     N     Y      Y   N   T
ExampleTableWithKeywordColumnNames VALUES  N     N      Y   N   T

As you can see the example table shows up as well as the earlier example table. This UDF has saved me a bunch of rework when columns won't work in the business object layer.

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