fn_listextendedproperty

Returns extended property values of database objects.

Syntax

fn_listextendedproperty (
    { default | [ @name = ] 'property_name' | NULL }
    , { default | [ @level0type = ] 'level0_object_type' | NULL }
    , { default | [ @level0name = ] 'level0_object_name' | NULL }
    , { default | [ @level1type = ] 'level1_object_type' | NULL }
    , { default | [ @level1name = ] 'level1_object_name' | NULL }
    , { default | [ @level2type = ] 'level2_object_type' | NULL }
    , { default | [ @level2name = ] 'level2_object_name' | NULL }
    )

Arguments

{default|[@name =] 'property_name'|NULL}

Is the name of the property. property_name is sysname. Valid inputs are default, NULL, or a property name.

{default|[@level0type =] 'level0_object_type'|NULL}

Is the user or user-defined type. level0_object_type is varchar(128), with a default of NULL. Valid inputs are USER, TYPE, default, and NULL.

{default|[@level0name =] 'level0_object_name'|NULL}

Is the name of the level 0 object type specified. level0_object_name is sysname with a default of NULL. Valid inputs are default, NULL, or an object name.

{default|[@level1type =] 'level1_object_type'|NULL}

Is the type of level 1 object. level1_object_type is varchar(128) with a default of NULL. Valid inputs are TABLE, VIEW, PROCEDURE, FUNCTION, DEFAULT, RULE, default, and NULL.

Note Default maps to NULL and 'default' maps to the object type DEFAULT.

{default|[@level1name =] 'level1_object_name'|NULL}

Is the name of the level 1 object type specified. level1_object_name is sysname with a default of NULL. Valid inputs are default, NULL, or an object name.

{default|[@level2type =] 'level2_object_type'|NULL}

Is the type of level 2 object. level2_object_type is varchar(128) with a default of NULL. Valid inputs are COLUMN, PARAMETER, INDEX, CONSTRAINT, TRIGGER, DEFAULT, default (which maps to NULL), and NULL.

{default|[@level2name =] 'level2_object_name'|NULL}

Is the name of the level 2 object type specified. level2_object_name is sysname with a default of NULL. Valid inputs are default, NULL, or an object name.

Tables Returned

This is the format of the tables returned by fn_listextendedproperty.

Column name	Data type
objtype	sysname
objname	sysname
name	sysname
value	sql_variant

If the table returned is empty, either the object does not have extended properties or the user does not have permissions to list the extended properties on the object.

Remarks

Extended properties are not allowed on system objects.

If the value for property_name is NULL or default, fn_listextendedproperty returns all the properties for the object.

When the object type is specified and the value of the corresponding object name is NULL or default, fn_listextendedproperty returns all extended properties for all objects of the type specified.

The objects are distinguished according to levels, with level 0 as the highest and level 2 the lowest. If a lower level object (level 1 or 2) type and name are specified, the parent object type and name should be given values that are not NULL or default. Otherwise, the function will return an error.

Permissions to list extended properties of certain level object types vary.

For level 0 objects, a user can list extended properties specifying the type "user" if that person is the user identified in the level 0 name, or if that user is a member of the db_owner and db_ddladmin fixed database role.
All users can list extended properties using the level 0 object type "type."
For level 1 objects, a user can list extended properties on any of the valid type values if the user is the object owner, or if the user has any permission on the object.
For level 2 objects, a user can list extended properties on any of valid type values if the current user has any permission on the parent object (level 1 and 0).

Examples

This example lists all extended properties for the database.

SELECT   *
FROM   ::fn_listextendedproperty(NULL, NULL, NULL, NULL, NULL, NULL, NULL)

-Or-

SELECT   *
FROM   ::fn_listextendedproperty(default, default, default, default, default, default, default)

This example lists all extended properties for all columns in table 'T1.'

CREATE   table T1 (id int , name char (20))

EXEC   sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo, 'table', 'T1', 'column', id

EXEC   sp_addextendedproperty 'caption', 'Employee Name', 'user', dbo, 'table', 'T1', 'column', name

SELECT   *
FROM   ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'T1', 'column', default)

Here is the result set: