Find Column Names that don't Port to DB2

As I mentioned last week, I've been working with a linked DB2 database running on an iSeries (f.k.a AS/400 f.k.a Series/38 f.k.a Series/3). There are limitations to the linked server implementation when it comes to INSERTs, UPDATEs, and DELETEs that cause us to want our client side code to work with tables sometimes as SQL Server views to the linked tables but sometimes directly to the DB2 database. To get the client code to work, we've modified it so that it doesn't bracket all table names and column names in the SQL statements that it generates. For example,

    SELECT [mytable].[mycol] FROM [mytable]

    SELECT mytable.mycol FROM mytable

We have to do this because DB2 doesn't seem to like the brackets. Although there are alternative ways to code this we decided to go with dropping the brackets for ease of coding and portability. Of course, we only did this after a little analysis that showed that we didn't have a problem with spaces or special characters in column names.

The analysis consisted of running this week's UDF, udf_Tbl_ColNotPortable, which returns a table of the columns in a database that have spaces or certain special characters in their name. These columns would require quotations in order to be used in valid SQL statements with either SQL Server or DB2. To get this UDF to compile you'll need four UDFs from previous issues. udf_Txt_HasCharInGroup is included in Volumn 1 #40.  udf_Tbl_ColInfoTAB is from Volume 2 #12. But udf_Tbl_ColInfoTAB needs two UDFs itself: udf_SQL_DatatypeSTring from Volume 1 #18 and udf_Tbl_ColDescriptionsTAB from Volume 2 #11.

Once you're sure you have those UDFs in place, here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE   FUNCTION dbo.udf_Tbl_ColNotPortable (

) RETURNS TABLE
/*
* Returns a table including columns that don't have portable
* names because they have blanks or special characters in the
* column name.
*
* Example:
SELECT * FROM dbo.udf_Tbl_ColNotPortable()
*      
* © 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 #21 05/11/04 
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
***************************************************************/
AS RETURN 

SELECT TABLE_NAME
     , COLUMN_NAME
     , CASE WHEN CHARINDEX(' ', COLUMN_NAME)>0 THEN 'SPACE'
            WHEN CHARINDEX('-', COLUMN_NAME)>0 THEN 'Minus'
            WHEN CHARINDEX('+', COLUMN_NAME)>0 THEN 'Plus'
            WHEN CHARINDEX(':', COLUMN_NAME)>0 THEN 'Colon'
            WHEN CHARINDEX(';', COLUMN_NAME)>0 THEN 'Semicolon'
            WHEN CHARINDEX('=', COLUMN_NAME)>0 THEN 'equals'
            WHEN CHARINDEX('[', COLUMN_NAME)>0 THEN 'Left Brkt'
            WHEN CHARINDEX(']', COLUMN_NAME)>0 THEN 'Rt Brkt'
            WHEN CHARINDEX('(', COLUMN_NAME)>0 THEN 'Left Paren'
            WHEN CHARINDEX(')', COLUMN_NAME)>0 THEN 'Rt Paren'
            WHEN CHARINDEX('|', COLUMN_NAME)>0 THEN 'Pipe'
            WHEN CHARINDEX('\', COLUMN_NAME)>0 THEN 'Backslash'
            WHEN CHARINDEX('/', COLUMN_NAME)>0 THEN 'Divide'
            WHEN CHARINDEX('?', COLUMN_NAME)>0 THEN 'Question'
            WHEN CHARINDEX(',', COLUMN_NAME)>0 THEN 'Comma'
            WHEN CHARINDEX('.', COLUMN_NAME)>0 THEN 'period'
            WHEN CHARINDEX('<', COLUMN_NAME)>0 THEN 'LT'
            WHEN CHARINDEX('>', COLUMN_NAME)>0 THEN 'GT'
            ELSE 'unknown' END AS [Fst_Prblm]
     , ORDINAL_POSITION as [Pos]
     , Data_Type as [Type]
     , [Description]
    FROM dbo.udf_Tbl_ColInfoTAB   (null, null)
    WHERE 1=dbo.udf_Txt_HasCharInGroup(Column_Name
                                       , ' -+:-=[]()|\/?,.<>')
GO

GRANT SELECT on dbo.udf_Tbl_ColNotPortable TO PUBLIC
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Columns from both tables and views are returned. I didn't intend it but that feature turns out to be a benefit because the only place that the database that I was working on had any spaces in a column name is in a couple of views. Here's a script that shows the results from the TSQLUDFS database that comes with my book:

SELECT * FROM dbo.udf_Tbl_ColNotPortable()
GO

(Results - reformatted with some names shortened)
TABLE_NAME          COLUMN_NAME  Fst_Prblm Pos  Type        Desc
------------------- ------------ --------- ---- ----------- ----
ExampleVie....sage  Event Logged SPACE        4 varchar(15) NULL

Running this UDF gave my team the assurance that we could proceed with our plan for portable SQL that works against both SQL Server and DB2.

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