Searching column description description meta across all tables

Question

I am considering using extended properties to store table and column descriptions but I want to be able to search the descriptions across for ALL tables looking for the occurence of a particular substring.

In the following example, the function shown returns a list the column descriptions for the table "PEOPLE." These descriptions are stored as as extended properties with name "MS_DESCRIPTION."

   SELECT
    cast(VALUE AS VARCHAR(8000)) AS [DESCRIPTION]
                     FROM
                         ::fn_listextendedproperty(NULL
                                                   ,'user'
                                                   ,'dbo'
                                                   ,'table'
                                                   ,'PEOPLE'
                                                   ,'column'
                                                   ,NULL)

However, how do I search all column descriptions across all tables for a given substring?

SELECT
    cast(VALUE AS VARCHAR(8000)) AS [DESCRIPTION]
                     FROM
                         ::fn_listextendedproperty(NULL
                                                   ,'user'
                                                   ,'dbo'
                                                   ,'table'
                                                   ,'?'
                                                   ,'column'
                                                   ,NULL)
    where cast(VALUE AS VARCHAR(8000)) LIKE '%SEARCH%'

Is there a more efficient way to search that would avoid a conversion of teh VALUE field from a SQL_VARIANT to a varchar?

Would it be wiser to store this meta data in user defined custom tables instead of using extended properties?

score 9 · Accepted Answer · answered Feb 21 '13 at 17:45

9

You can look at the system views to search for all columns across all tables.

SELECT schemas.name schemaName
     , tables.name tableName
     , columns.name columnName
     , extended_properties.value extendedProperties
  FROM sys.schemas
 INNER JOIN sys.tables
    ON schemas.schema_id = tables.schema_id
 INNER JOIN sys.columns
    ON tables.object_id = columns.object_id
 INNER JOIN sys.extended_properties
    ON tables.object_id = extended_properties.major_id
   AND columns.column_id = extended_properties.minor_id
   AND extended_properties.name = 'MS_Description'
   AND CAST( extended_properties.value AS nvarchar(max) ) LIKE '%SEARCH%';

answered Feb 21 '13 at 17:45

Drew Leffelman

516
2
8

Thanks. That worked. As a follow up, I am wondering how one can look at the code for the listeextendedproperty system function found under Programmatability/Functions/System Function/MetaData Functions? – Chad Feb 21 '13 at 17:49
You can use SELECT OBJECT_DEFINITION(OBJECT_ID('sys.fn_listextendedproperty')); to see the code. This came from http://msdn.microsoft.com/en-us/library/ms190940%28v=sql.110%29.aspx. – Drew Leffelman Feb 21 '13 at 17:59

score 1 · Answer 2 · answered Aug 27 '13 at 14:44

To find columns of a certain name, user1948904's solution gets a little simpler:

SELECT schemas.name schemaName
   , tables.name tableName
   , columns.name columnName
   , * -- for context if you like
 FROM sys.schemas
 INNER JOIN sys.tables
   ON schemas.schema_id = tables.schema_id
 INNER JOIN sys.columns
   ON tables.object_id = columns.object_id
 WHERE columns.name LIKE '%SEARCH%'

The search is case insensitive.

Searching column description description meta across all tables

2 Answers2

Linked