Extended properties on table columns

clamk123 · June 16, 2015 12:14PM

How can I access extended properties on tables programmatically?

If I go through the ExtendedProperties it has the level1types and level0type information, but the value doesn't seem to appear for that property. Are extended properties accessed elsewhere since this was deprecated in SDK 10? These would be column extended properties such as:

EXEC sys.sp_addextendedproperty @name = N'MS_DescriptionExample', @value = N'User first name', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'TABLE', @level1name = 'MyTable', @level2type=N'COLUMN', @level2name=N'FirstName';

Mike U · June 17, 2015 8:20AM

ExtendedProperty (the type of the items within an ExtendedProperties collection) has a Value property, which should give you what you want. The ExtendedProperties collection is defined on all SchemaObject derived classes, so the following example code would dump all extended properties of a SchemaObject to the console:

void DumpExtendedProperties(SchemaObject schemaObject)
{
    Console.WriteLine("{0} has {1} extended property(ies):", 
                      schemaObject.FullyQualifiedName,
                      schemaObject.ExtendedProperties.Count);

    foreach (var extendedProperty in schemaObject.ExtendedProperties)
    {
        Console.WriteLine("- {0}", extendedProperty.FullyQualifiedName.Replace("", "|"));
        Console.WriteLine("  Value: "{0}" of type: {1}", 
                          extendedProperty.Value.GetSQLValue(), 
                          extendedProperty.Value.GetSQLTypeString());               
    }
}

Extended properties on table columns

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk