Correlating the SysObjects XType column to the Extended Properties level1_object_type value RRS feed

  • Question

  • I'm trying to correlate the list from this query: 

    SELECT name  FROM master..spt_values WHERE type = 'O9T'

    to the values that pertain to Extended Properties:

    [ @level1type= ] { 'level1_object_type' } Is the type of level 1 object. level1_object_type is varchar(128), with a default of NULL. Valid inputs are AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, SEQUENCE, SYNONYM, TABLE, TABLE_TYPE, TYPE, VIEW, XML SCHEMA COLLECTION, and NULL. [ @level1name= ] { 'level1_object_name' } Is the name of the level 1 object type specified. level1_object_name is sysname, with a default of NULL.

    So that I can Add Extended properties to our objects in a SQL Server database using the stored procedure: 

    If (@RowCount=0 or @RowCount is null) and @action='A'

    EXEC sys.sp_addextendedproperty

    @name = 'Action', --Extended Property Name

    @value = @v1_ActionToDo,  --Value

    @level0type = N'SCHEMA', @level0name = 'dbo',

    @level1type = @level1type,  --Object Type

    @level1name = @v_level1name   -- Object Name

    In the case of a View, the Xtype column of the SysObjects equates to "V",

    as do the results of:

    SELECT name  FROM master..spt_values WHERE type = 'O9T' and name='V'

    Whereas the sys.sp_addextenededproperty requires not the use of 'V' for the parameter @level1type, but instead requires the value: "VIEW" instead. 

    I'd like to correlate the values between the Xtype in SystemObjects to the nomenclature required in the sp_addextendedproperty stored procedure so a routine can be automated to add new extended properties to various specific objects in our database. 

    Where can I find the corresponding definition values to use in sp_addextendedproperty ?

    Views are obvious, Tables and stored procedures are as well. It starts getting a bit murky with Scalar Valued Functions, Table-valued functions, etc.

    Thank you

    Friday, January 17, 2020 4:06 PM

All replies

  • Hi vsdla, 

    Sorry for my poor understanding . 

    What is your requirement? Could you explain more ? 

    Best Regards,


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Monday, January 20, 2020 6:04 AM