Get Descriptions for TABLE Columns.

Last issue featured udf_EP_AllTableLevel2EPsTAB which returns the extended properties for table objects. This issue is part of a series that's building up to udf_Tbl_ColInfoTAB, which will show information about table columns including the descriptions that are entered using SQL Enterprise Manager.

This issue's UDF uses udf_EP_AllTableLevel2EPsTAB to retrieve the descriptions for all columns in either a particular table, or all tables. Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET NOCOUNT ON
GO

CREATE  FUNCTION dbo.udf_Tbl_ColDescriptionsTAB (

    @TableOwner sysname = NULL -- Onwer to search for
  , @TableName sysname = NULL -- Table to query or NULL=all
)   RETURNS TABLE -- EPs for all COLUMNS in the table(s)
  -- NO SCHEMABINDING, using INFORMATION_SCHEMA
/*
* Returns the description extended property for all columns of 
* user tables in the database.  A specific owner or table can 
* be named. If @TableOwner is null, extended properties for all
* owners is returned. If @TableName is NULL, columns for all 
* tables are returned.
*
* Example: 
select * from udf_Tbl_ColDescriptionsTAB(NULL, NULL) 
   -- Descriptions for all columns in all tables.
*
* © 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 #11 3/2/04
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS RETURN SELECT [Owner]
               , [TableName]
               , objname as [ColumnName]
               , CAST(Value as nvarchar(255)) as [Description]
             FROM udf_EP_AllTableLevel2EPsTAB 
                                      ('MS_Description'  
                                      , @TableOwner
                                      , @TableName
                                      , 'COLUMN'         
                                      , NULL
                                      )

GO

GRANT SELECT on dbo.udf_Tbl_ColDescriptionsTAB TO PUBLIC
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Using udf_Tbl_ColDescriptionsTAB is really the same as running last issue's example query. It uses udf_EP_AllTableLevel2EPsTAB and specifies the 'MS_Description' extended property and the 'COLUMN' object. Here's the sample query using udf_Tbl_ColDescriptionsTAB:

select Owner, TableName, ColumnName as [Column Name]
            , [Description]
            from udf_Tbl_ColDescriptionsTAB(
                                    NULL -- All Owners/Schema
                                  , NULL -- All Table Names
                                )
 GO

Owner TableName     Column Name         Description
----- ------------- ------------------- -------------------------
dbo   AddrCountryCD CountryISO3166A2CD  Two Character Code
dbo   AddrCountryCD CountryISO3166A3CD  Three character code
dbo   AddrCountryCD CountryName         Country Name
dbo   AddrCountryCD UNCountryID         United Nations assigned 3
dbo   CalendarEvent CreatedBYUSERID     Login that created the ro
dbo   CalendarEvent CreatedDT           Datetime the row was crea
dbo   CurrencyCD    Comment             Descriptive comment
dbo   CurrencyCD    CurrencyCD          ISO 4217 Currency Code us
dbo   CurrencyCD    CurrencyName        Common Name for the curre
dbo   CurrencyCD    IssuingGovernment   Country or organization i
dbo   CurrencyCD    NickName            Shorter name for common u
dbo   CurrencyCD    Symbol              Graphic symbol used to de

Next issue we'll use udf_Tbl_ColDescriptionsTAB to build udf_Tbl_ColInfoTAB. It will be used to respond to the original request, which involved searching for all the columns of a particular name so that their data types could easily be modified in unison.

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