Count the number of columns in each TABLE

I've recently embarked on an effort to build client side business objects using the CSLA methodology for a DB2 database running on an iSeries (f.k.a. AS/400) midrange server. The objects are built in VB.Net and access data directly on the iSeries.

The DB2 database has a long evolutionary history dating back to the mid 1970's. Over this history various programming practices were employed that are no longer considered good programming practices but in their day the served to simplify the RPG programs that supported the company effectively. I'm talking about practices like denormalinazation of data. In particular, the developers of these tables seemed to like repeating groups where you'd have a group of variables with a number and at the end of each group you'd have a different digit. Of course, you had to decide the maximum number of groups that you were going to support and add that many sets of variables. For example, one set might be MCRPH1 MCRCP1 MCRCD1 followed by MCRPH2 MCRCP2 MCRCD2, and MCRPH3 MCRCP3 MCRCD3. I'm sure most of the old timers out there have seen this many times before.

In the relational world, we'd normalize this structure and both shrink the table and be able to provide an unlimited number of groups if desired. However, when these programs were written using a pre-relational technology every join would have to be programmed and it was just much faster to write the code with these repeating groups.

As we try and build the business objects for these tables, one concern that my client had was the number of columns in some of the tables. You see, in order to get our code generator (ComExpress) to build the business objects we have to script the tables to SQL Server so that the generator can read the schema information. Could SQL Server handle tables with several hundred columns?

It turns out that SQL Server allows base tables to contain up to 1024 columns. There was not problem transferring the schema information.

This week's UDF, udf_TBL_ColumnCountTAB, counts the number of columns in the base tables of a database. We used to verify that there were no tables with more than 1024 columns. Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER  FUNCTION dbo.udf_Tbl_ColCountTAB (

    @Table_Name_Pattern sysname = NULL -- Pattern for matching 
                                -- to the table name, Null=all
  , @Col_Name_Pattern sysname  = NULL -- Pattern for matching 
                                -- to the column name, NULL=all
)   RETURNS TABLE
    -- NO SCHEMABINDING do to use of INFORMATION_SCHEMA
/* 
* Returns a count for the number of columns in each table or
* view that satisfies the Table_Name_Pattern
*
* Example:
select * FROM dbo.udf_Tbl_ColCountTAB   (null, null)
*
* © 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 #27
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS RETURN

SELECT TOP 100 PERCENT WITH TIES
        c.TABLE_NAME
      , COUNT(*) NumColumns
    FROM  INFORMATION_SCHEMA.[COLUMNS] c
        inner join INFORMATION_SCHEMA.[TABLES] t
           ON  C.TABLE_SCHEMA = t.TABLE_SCHEMA
           and c.TABLE_NAME = T.TABLE_NAME
    WHERE TABLE_TYPE = 'BASE TABLE'
       and 0=OBJECTPROPERTY(OBJECT_ID(t.TABLE_NAME),
                            'IsMSShipped')
       and (@Table_name_pattern IS NULL 
              or c.TABLE_NAME LIKE @Table_Name_Pattern)
       and (@Col_Name_Pattern IS NULL 
              or c.COLUMN_NAME LIKE @Col_Name_Pattern)
    GROUP BY c.TABLE_NAME
    ORDER BY c.TABLE_NAME

GO

GRANT SELECT ON dbo.udf_Tbl_ColCountTAB to PUBLIC
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Let's execute the function on the development database it was built for:

SELECT * FROM udf_Tbl_ColCountTAB (default, default)
GO


(Results - partial)
TABLE_NAME                  NumColumns  
--------------------------- ----------- 
AppConfiguration                     19 
AppEvent                             28 
AppEventStep                         20 
AppEventStepDetail                   10 
AppEventStepParameter                11 
AppFile                              13 
AppLock                              12 
AppLockConflict                      16 
AppLockConflictCD                     3 
AppLockID                            10 
AppLockIDSegment                      8 
AppVBNetKeywordColumns              136 
AppVersion                            8 
BoilerPlate                          29
Company                               4 
CUSTADDR                              5 
CUSTMSTR                            139 
ProductLineMatrix                     7 
ProductType                           7 
PTBKDWN                             311 
PTBKMSTR                             13 
PTBKSBHD                              7 
PTDETAIL                            121 
PTMASTER                            227 
PTMASTERPR                           20 
SALESMAN                              6 

As these results show, the tables from the iSeries (those with names in all capitol letters) don't have any where near the number of columns that would cause a problem for SQL Server.

There are a few conditions in the WHERE clause that are worth a note. In particular these:

    WHERE TABLE_TYPE = 'BASE TABLE'
       and 0=OBJECTPROPERTY(OBJECT_ID(t.TABLE_NAME),

The first condition filters out any VIEWS that are returned by INFORMATION_SCHAMA.TABLES, which includes both tables and views. The second condition, 0=OBJECTPROPERTY(OBJECT_ID(t.TABLE_NAME), filters out any system tables. Of course, INFORMATION_SCHEMA doesn't report on system tables such as sysobjects or sysfiles. However, it does include information on dtproperties, which is stored in sysobjects as a user table, even though it's part of SQL Server. The IsMSShipped test filters it out.

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