Extended properties on table columns

clamk123clamk123 Posts: 40
edited June 17, 2015 8:20AM in SQL Comparison SDK 11
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';

Comments

  • 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());               
        }
    }
    
    Development Lead
    Redgate Software
Sign In or Register to comment.