Find Columns with Inconsistent Definition (Data Type)

A few weeks ago I started on this series of UDFs devoted to helping a reader find the columns in his database where there were multiple columns with the same name but different data types. It's a pretty good design practice to use the same name for the same thing every time although it's a policy that can be taken to far. After all, Description is an appropriate name for a column that might require anywhere from 10 to 8000 characters.

On the other hand, there are times where this is a serious design problem such as when the columns are suppose to be used in a foreign key relationship and they can't because of the data type difference.

Because this function builds on other UDFs, you'll need to add them to your database before you can create this one. You'll need Vol 2 #10, #11, and #12 as well as udf_SQL_DataTypeString from Vol 1 #13. Be sure you have them before trying to run the script. Here's the CREATE FUNCTION script for this issue's UDF:

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET NOCOUNT ON
GO

CREATE  FUNCTION dbo.udf_Tbl_ColPotentialProblemsTAB (

)   RETURNS TABLE
    -- NO SCHEMABINDING do to use of INFORMATION_SCHEMA
/* 
* Returns a list of columns where there is more than one 
* column using that name in the database and all of the columns
* don't have the same data type.  These are potential design 
* problems.
*
* Example:
select * FROM dbo.udf_Tbl_ColPotentialProblemsTAB() 
*
* History:
* When          Who     Description
* ------------- ------- -----------------------------------------
* 2004-01-27    ASN     Initial Coding
*
* © 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 Vol 2 #13 3/16/04
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS RETURN

SELECT  TOP 100 PERCENT WITH TIES
        C2.COLUMN_NAME
      , C2.Data_Type
      , C2.TABLE_NAME
      , C2.Description
    FROM dbo.udf_Tbl_ColInfoTAB(null, null) C2
    INNER join  (SELECT COLUMN_NAME, Count(* ) cnt
                    FROM (SELECT COLUMN_NAME, Data_Type 
                             FROM dbo.udf_Tbl_ColInfoTAB(null, null)
                             GROUP BY COLUMN_NAME, Data_Type
                          ) C0
                    GROUP BY COLUMN_Name
                    HAVING Count(*) > 1
                ) C1
        ON C2.COLUMN_NAME=C1.COLUMN_NAME
        ORDER BY C2.COLUMN_NAME
        
GO

GRANT SELECT on dbo.udf_Tbl_ColPotentialProblemsTAB TO PUBLIC
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

There are no parameters to the UDF so we get all the information we need with this simple query:

select * FROM dbo.udf_Tbl_ColPotentialProblemsTAB() 
GO

COLUMN_NAME Data_Type     TABLE_NAME          Description 
----------- ------------- ------------------- -------------------
City        varchar(20)   Cust                NULL
City        nvarchar(32)  ExampleAddresses    NULL
CountryName nvarchar(65)  ExampleAddresses    NULL
CountryName varchar(100)  AddrCountryCD       Country Name
CustomerID  int           Cust                NULL
CustomerID  nchar(5)      NWOrders            NULL
Description varchar(2000) UnitCategoryCD      What type of unit
Description varchar(255)  PhoneAreaCodes      Where is the code
Description varchar(128)  PhoneCountryCD      Special information
Description varchar(2000) CurrencyRateTypeCD  NULL
Description varchar(2000) CurrencySourceCD    Desc. of the source
Description varchar(255)  CalendarDefinition  Desc. of the calend
Description varchar(255)  CalendarEvent       Desc. of the event
FirstName   varchar(20)   ExampleNames        NULL
FirstName   varchar(40)   Broker              NULL
ID          int           TriggerTester       NULL
ID          int           TextFileContents    NULL
ID          numeric(18 0) TEST_IDENTITY       NULL
Name        varchar(32)   UnitsDefinition     NULL
name        nvarchar(128) object_versions     NULL
Name        varchar(24)   CalendarLocationCD  NULL
Name        varchar(16)   CurrencyRateTypeCD  Descriptive name
Name        varchar(32)   CalendarDefinition  NULL

The results are for my working database so the problems here aren't going to hurt in practice. However, if this were a real application you might want to address the issue with the ID columns, the Name column, country names, city, and CustomerID.

This UDF is certainly worth adding to your arsenal and running while you're developing the database and maybe occasionally thereafter.

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