|Business Application Development with: SQL Server, C#, VB, VB.Net, ASP, ASP.Net, and XML|
|News Links Schedule Site Map Contact|
SQL Server T-SQL User-Defined Function of the Week
Get Descriptions for TABLE Columns.Volume 2 Number 11 March 2, 2004
Check out the UDF Frequently Asked Questions at:
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(Results - Truncated on the right...)
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: