Listing of tables using extended properties

• dean gross

  SSCrazy

  Points: 2632

  More actions

  December 22, 2004 at 4:32 pm

  #162961

  How can I extract a list of tables from a database that have specific values in an extended property, e.g., I am thinking about creating an extended property for the type of table (i.e, lookup, summary, etc) and then I would like to be able to easily identify all of the tables that are of a certain type.

  TIA

  Dean

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  December 23, 2004 at 12:26 pm

  #534925

  How abut this for a starting point:

  Dynamic TSQL for extended properties

  Select * from ::fn_listextendedproperty(NULL, 'user', 'dbo' ,'table', 'agents','column',Default)

  Hopes this helps 

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• jarretg

  SSCommitted

  Points: 1686

  More actions

  December 27, 2004 at 8:19 am

  #535042

  This will list the table and extended property for any table with the word 'lookup' in the extended property.

  select o.name as Table_name,

   p.value as Extended_Property

  from sysobjects o

   inner join sysproperties p on o.id = p.id

  where p.smallid = 0

   and convert(varchar, p.value) like '%lookup%'

  order by o.name

  Hope this helps.

  Jarret