fn_listextendedproperty sql 2005

fn_listextendedproperty sql 2005

• Terp

  Oh, I see what I'm doing wrong.  I'm using the codeto extract a table level attribute when I want to extract all the attributes for all columns within a db.
  
  You wouldn't happen to know the parameters required for that would you   I'll play around and see if I can get it.
  
  Regards,
  
  Ned
  

• ian530555

  Hi,
  
  Yup, I'm sure. I have 80 tables and most of them have comments on each column. 
  
  I executed in aventureworks and got the same results you did.
  
  However, in my database, if I specify a specific table:
  
  SELECT objtype, objname, name, value
  FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', 'Agency', 'column', default);

  
  I get the extended attribute values.
  
  However, if I use the code that specifies it should retrieve the data for all columns, it doesn't work (I get nothing back):
  
  SELECT objtype, objname, name, value
  FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', default, NULL, NULL);
  
  
  I decided to create another db.  And the second query does not list out the extended attributes in the new db either...just the first query where I specify table name.
  
  
  Regards,
  
  Ned

  
  

• Philip York

  Are you sure you have some extended properties defined on tables If you try above query in AdventureWorks db you will get the data correctly for tables in dbo schema.
  
  

• OliNagel

  Thanks very much Julian,
  
  I ended up writing a cursor, but this is more elegant.
  
  
  Regards,
  
  Ned
  

• timothy leung

  If you want to select the extended properties for all columns in all tables you can use:
  
  

  SELECT OBJECT_NAME(EXP.major_id) AS TableName,
   C.name AS ColumnName,
   EXP.name AS PropertyName, 
   EXP.value AS PropertyValue 
  FROM sys.extended_properties AS EXP 
  LEFT OUTER JOIN sys.columns AS C 
  ON C.object_id = EXP.major_id 
  AND C.column_id = EXP.minor_id 
  WHERE EXP.class_desc = 'OBJECT_OR_COLUMN'

  
  
• fn_listextendedproperty sql 2005