SQL sp_addextendedproperty issues

Question

Good afternoon,

I have a table called assurance.SelfService_Patients with around 23 columns, 22 of which I want to add metadata too, in order to explain what each column is.

At the moment I am struggling with the syntax. I have attempted the following:

EXEC sp_addextendedproperty
     @name = N'SPLV' 
    ,@value = N'The SPL version that the dataset belongs to. A new SPL version typically runs every week on a Tuesday.'
    ,@level0type = N'Schema', @level0name = 'assurance'
    ,@level1type = N'Table',  @level1name = 'SelfService_Patients' 
    ,@level2type = N'Column', @level2name = 'SPLV'
         
go  

This runs successfully but I would have expected it to populate the properties of the table, (when you right click, properties, extended properties it still shows blank)

I would have also expected the following to extract something rather than a blank result:

select * 
from sys.extended_properties 
where name = 'SelfService_Patients'

Where am I going wrong please, it's incredibly frsutrating.

Regards, Andrew.

Answer 1

You are querying by the name of the property which is not what you want. You either need to use the property name like this.

select * 
from sys.extended_properties 
where name = 'SPLV'

Or what I suspect is you want to view all the extended properties defined on a specific table. You need to look at major_id for this.

select * 
from sys.extended_properties 
where major_id = OBJECT_ID('assurance.SelfService_Patients')

This is all pretty clear in the documentation. https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/extended-properties-catalog-views-sys-extended-properties?view=sql-server-ver15