Large enterprise databases will often have columns that are repeated in many tables throughout the schema. These columns, while containing unique data, may have identical properties.  Maintenance of these columns can be challenging, especially if you are not the one who is as intimately familiar with the database schema as the original architect might be.

The brief case study below illuminates how this can be challenging:

Joe Deebeay maintains an enterprise database that contains over 200 tables. Joe’s manager has stated that the user interface of the application will begin to pass all of the zip codes with ten characters.

In the original design of this database’s schema, the original architect, who was not Joe, determined that the data type of the zip code fields should be varchar(5). This size accommodates the standard zip code in the format of “46204”. The new format will require Joe to increase the column’s size to varchar(10) which accommodates the zip code format of “46204-0101”.

With over 200 tables, finding all of the occurrences of columns that contain zip codes is no small undertaking; but using the system tables of SYSCOLUMNS and SYSOBJECTS might just allow Joe to enjoy his tacos at lunch rather than skipping it altogether.

Joe might want to first see what columns exist with the size of five characters. Below is a sample of the SQL Statement that Joe might utilize:

        SELECT
             B.NAME AS TABLE_NAME,
             A.NAME AS COLUMN_NAME
        FROM
             SYSCOLUMNS A
             INNER JOIN SYSOBJECTS B
                 ON A.ID = B.ID
                 AND B.XTYPE = 'U'
        WHERE
            A.LENGTH = 5

After reviewing the results of this query Joe might want to filter the results to columns that contain the word “zip” in the column name. Below is a sample of the SQL Statement that Joe might utilize:

        SELECT
            B.NAME AS TABLE_NAME,
            A.NAME AS COLUMN_NAME
        FROM
            SYSCOLUMNS A
            INNER JOIN SYSOBJECTS B
                ON A.ID = B.ID
                AND B.XTYPE = 'U'
        WHERE
            A.NAME LIKE ‘%zip%’
            AND A.LENGTH = 5

Now that Joe has an idea of the scope of his changes he can then be confidently made with less risk of oversight. Joe is happy. Joe’s manager is happy. Life is good. :D