Getting Extended Properties for Table COLUMNS, INDEXES, and Triggers

I got this note from a reader earlier this week:

I frequently use your UDF for finding stored procedures that use a field name that I need to trace the use of. Can I search for field names in tables? I need to widen a field and do it everywhere that the value may be written to.

Thanks, Jeb Bonsteel, Coventry Resources

The udf that Jeb mentioned is udf_SQL_SearchDBObjectsTAB from Volume 1 Issue #13. It searches the text of syscomments to find a the objects that include a string. It can't be used to find tables that reference a column name because syscomments doesn't store the dentition for tables, only for views, rules, defaults, triggers, CHECK constraints, DEFAULT constraints, and stored procedures.

To get the desired information, the best place to query is the INFORMATION_SCHEMA.COLUMNS view, which returns information about all columns from all tables. There are additional views to get the columns from views and procedures: INFORMATION_SCHEMA.VIEW_COLUMN_USAGE and INFORMATION_SCHEMA.ROUTINE_COLUMNS respectively.

I have a UDF, udf_TBL_ColInfoTAB that includes the information needed to find all columns with a name and show the data type used to define it and the description, if any, for the column. In order to use udf_TBL_ColInfoTAB several other UDFs must be defined. So as my sons' favorite author, Dav Pilkey, often writes, before I can tell you that story, I have to tell you this story....

SQL Server 2000 introduced extended properties as way to associate metadata with database objects without modifying the system tables. SQL Server uses extended properties for storing the description information for tables and columns as the extended property 'MS_Description'. Chapter 15 of Transact-SQL User-Defined Functions is all about extended properties and how to retrieve them using the system UDF fn_getextendedproperty. The chapter also covers how to create and modify extended properties with system stored procedures. The explanation of fn_getextendedproperty is long so I won't go into it here but we'll need this week's UDF, udf_EP_AllTableLevel2EPsTAB, to get the description properties for columns. Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET NOCOUNT ON
GO

CREATE     FUNCTION dbo.udf_EP_AllTableLevel2EPsTAB (

    @epname sysname = NULL -- name of EP desired, NULL=all.
  , @InputTableOwner sysname = NULL -- name of owner, NULL=all.
  , @InputTableName sysname = NULL -- Table desired or NULL=all.
  , @level2_object_type varchar(128) = NULL -- Which subobjects 
    -- NULL is for EPs associated with the table: COLUMN, INDEX
    -- CONSTRAINT, or TRIGGER
  , @level2_object_name sysname = NULL -- level 2, Null=all.
)   RETURNS @EP TABLE ( Owner sysname -- The owner/schema name
                      , TableName sysname -- the Table Name
                      , objtype varchar(128)
                      , objname sysname
                      , [name] sysname -- name of extended prop.
                      , value sql_variant
                  )
    -- No SCHEMABINDING when using INFORMATION SCHEMA
/*
* Returns a table of extended properties for a property (or null 
* for all), for an owner (or null for all), for a table (or null 
* for all) in the database.  The Level 2 object name must be 
* specified (Null means on the table it self).  The Level 2 
* object name may be given to get info specific Level 2 object, 
* or use NULL for all level 2 objects.
*
* Example: all extended properties on columns for all tables.
select * from udf_EP_AllTableLevel2EPsTAB('MS_Description'
                                  , NULL -- All Owners/Schema
                                  , NULL -- All Table Names
                                  , 'COLUMN' -- Level 2 Object
                                  , default -- All columns
                                )
*
* © 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 #10 2/24/04
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN 

    DECLARE @TableName sysname -- holds a table name
          , @TableOwner sysname -- holds the owner/user/schema

    DECLARE TableCursor CURSOR  FAST_FORWARD FOR 
        SELECT TABLE_NAME, TABLE_SCHEMA
            FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_TYPE = 'BASE TABLE'
              and (@InputTableOwner is NULL
                   or TABLE_SCHEMA = @InputTableOwner)
              and (@InputTableName is NULL 
                   or TABLE_NAME = @InputTableName)
            ORDER BY TABLE_SCHEMA, TABLE_NAME

    -- Open the cursor and fetch the first result
    OPEN TableCursor
    FETCH TableCursor INTO @TableName, @TableOwner

    WHILE @@Fetch_status = 0  BEGIN
        -- Get All EPs for this table and user

        INSERT INTO @EP    
            SELECT @TableOwner, @TableName
                   , objtype, objname
                   , [name], value 
                FROM  ::fn_listextendedproperty (@epname 
                               , 'USER', @TableOwner
                               , 'TABLE', @TableName
                               , @level2_object_type
                               , @level2_object_name)

        FETCH TableCursor INTO @TableName, @TableOwner -- nxt Tbl
    END -- of the WHILE LOOP

    -- Clean up the cursor
    CLOSE TableCursor
    DEALLOCATE TableCursor

    RETURN
END
GO

GRANT SELECT on dbo.udf_EP_AllTableLevel2EPsTAB TO PUBLIC
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

The following query asks for most of the columns from the function. I've left out objtype because it's always 'COLUMN' and name because in the case of this database, it's always 'MS_Description'. Here's the result of running the set's of sample numbers:

select Owner, TableName, ObjName, value
            from udf_EP_AllTableLevel2EPsTAB('MS_Description'
                                  , NULL -- All Owners/Schema
                                  , NULL -- All Table Names
                                  , 'COLUMN' -- Level 2 Object
                                  , default -- All columns
                                )
 GO

Owner TableName     objname             value    
----- ------------- ------------------- -------------------------
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

Now that we have a way to get the extended properties, we can move on to udf_Tbl_ColumnDescriptions, which will be next issue's UDF.

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