Get the information you need about your TABLE Columns

For the past two weeks, we've worked on retrieving the description extended property that can be added to table columns with Enterprise manager. We did that because this week's UDF pulls in the descriptions along with other information to give us a picture of the columns in a database.

The ultimate problem that I've been working on in these three issues is identifying the different data types that might be used by columns with the same name. A reader had the problem and, since the problem is common, I thought it would make a good series of issues.

This issue's UDF, udf_Tbl_ColInfoTAB, uses udf_Tbl_ColDescriptionsTAB to retrieve the descriptions for all tables and joins that information with other information from INFORMATION_SCHEMA.COLUMNS. You'll also need udf_SQL_DataTypeString from Vol 1 #18, which you can find on the web site if you didn't save it. Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET NOCOUNT ON
GO

CREATE  FUNCTION dbo.udf_Tbl_ColInfoTAB (

    @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 information for all columns including any
* description entered in Enterprise Managaer and and the data
* type of the column
*
* Example:
select * FROM dbo.udf_Tbl_ColInfoTAB   (null, null)
* Test:
* Test Script: TEST_udf_Tbl_ColInfoTAB
*
* 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 #12 3/9/04
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS RETURN

SELECT TOP 100 PERCENT WITH TIES
        TABLE_NAME
      , COLUMN_NAME
      , ORDINAL_POSITION
      , dbo.udf_SQL_DataTypeString (Data_Type
                              , character_maximum_length
                              , numeric_precision
                           , numeric_scale) as Data_Type
      , d.[Description]
    FROM  INFORMATION_SCHEMA.[COLUMNS] c
        LEFT OUTER JOIN udf_tbl_ColDescriptionsTAB 
                                       (default, default) d
               on c.TABLE_SCHEMA = d.owner 
                     and c.TABLE_NAME = d.TABLENAME
                     and c.COLUMN_NAME = d.ColumnName
    WHERE (@Table_name_pattern IS NULL 
              or TABLE_NAME LIKE @Table_Name_Pattern)
       and (@Col_Name_Pattern IS NULL 
              or COLUMN_NAME LIKE @Col_Name_Pattern)
    ORDER BY TABLE_NAME
           , ORDINAL_POSITION
GO

GRANT SELECT on dbo.udf_Tbl_ColInfoTAB TO PUBLIC
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Let's try it on the 'description' column name in one of my test databases:

select TABLE_Name, ORDINAL_POSITION as Pos
       , Data_Type
       , [Description] 
     FROM dbo.udf_Tbl_ColInfoTAB   (null, 'description')
     
GO

TABLE_NAME          POS Data_Type       Description 
------------------- --- --------------- -------------------------
AppMetadataDomain     2 varchar(2000)   NULL
CalendarDefinition    3 varchar(255)    NULL
CalendarEvent         9 varchar(255)    What happens during the e
CurrencyRateTypeCD    3 varchar(2000)   NULL
CurrencySourceCD      4 varchar(2000)   Description of the source.
PhoneAreaCodes        6 varchar(255)    Desc. of the coverage.
PhoneCountryCD        2 varchar(128)    Desc. of the country
UnitCategoryCD        2 varchar(2000)   Desc of the category

As you can see, the description varies in length from 128 to 2000 characters. That might not be a problem with descriptions, but it would be a problem with something like CompanyName or a column that is used in a foreign key relationship.

Next week we'll take this a little further and create a UDF that will identify the columns in a database where columns with the same name might have different data types. These are potential problems.

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