title: Searching column description description meta across all tables
body: <p>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.</p>
  
  <p>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." </p>
  
  <pre><code>   SELECT
      cast(VALUE AS VARCHAR(8000)) AS [DESCRIPTION]
                       FROM
                           ::fn_listextendedproperty(NULL
                                                     ,'user'
                                                     ,'dbo'
                                                     ,'table'
                                                     ,'PEOPLE'
                                                     ,'column'
                                                     ,NULL)
  </code></pre>
  
  <p>However, how do I search all column descriptions across all tables for a given substring?</p>
  
  <pre><code>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%'
  </code></pre>
  
  <p>Is there a more efficient way to search that would avoid a conversion of teh VALUE field from a SQL_VARIANT to a varchar?</p>
  
  <p>Would it be wiser to store this meta data in user defined custom tables instead of using extended properties?</p>