FN_ListextendedProperty
Returns the extension attribute value of the database object.
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})
Parameters {Default | [@Name =] 'Property_Name' | null}
Is the name of the property. Property_name is the sysname type. Effective input is default, null, or attribute name.
{Default | [@ level0type =] 'level0_object_type' | null}
User or user definition type. The data type of Level0_Object_Type is VARCHAR (128), whose default is NULL. Effective input is User, Type, Default and Null.
{Default | [@ level0name =] 'level0_object_name' | null}
The name of the specified 0-level object type. The data type of level0_object_name is sysname, whose default is NULL. Effective input is default, null or object name.
{Default | [@ level1type =] 'level1_object_type' | null}
Type 1 of Level 1. The data type of Level1_Object_Type is VARCHAR (128) whose default is NULL. Effective input is Table, View, Procedure, Function, Default, Rule, Default, and Null.
Description Default Mapping Null, and the "default" mapping object type Default.
{Default | [@ level1name =] 'level1_object_name' | null}
The name of the specified level 1 object type. The data type of level1_object_name is sysname, and its default value is NULL. Effective input is default, null or object name.
{Default | [@ level2type =] 'level2_object_type' | null}
Type 2 object. The data type of level2_object_type is VARCHAR (128), and its default value is NULL. Effective input is Column, Parameter, Index, Constraint, Trigger, Default, default, and null.
{DEFAULT | [@ level2name =] 'level2_object_name' | NULL} The name of the specified 2 object type. The data type of level2_object_name is sysname, and its default value is NULL. Effective input is default, null or object name.
The return table is the format of the table returned by FN_ListextendedProperty.
Column Data Type ObjType Sysname ObjName Sysname Name Sysname Value SQL_VARIANT
If the returned table is empty, the possible object does not have an extension attribute or the user who does not list the object extension attribute.
Note The system object does not allow an extended attribute.
If the value of the Property_Name is null or default, fn_listextendedProperty returns all properties of the object.
If the value of the object type is specified and the value of the corresponding object name is NULL or DEFAULT, fn_listextendedProperty returns all extension properties of all objects of the specified type.
Objects are distinguished by level, the 0 level is highest, and the 2 level is the lowest. If a lower level object (1 or level 2) type and name are specified, the value of the parent object type and name cannot be NULL or Default. Otherwise, the function will return an error.
The permissions for listing the extension attributes of certain level object types are different.
For the object of level 0, if a user is a user identified in the name of the level 0, or a member of the DB_OWNER and DB_DDLADMIN fixed database roles, the user can list the extension attribute of the specified "User" type.
All users can list the extension attribute using the 0-level object type "Type".
For Level 1 object, if the user has permissions for the object, the user can list the extension attribute with any valid type value.
For 2-level objects, if the current user has any permissions for the parent object (Level 1 and 0), the user can list the extended attribute with any valid type value. Example This example lists all extension properties of 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 all columns in 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)
The following is the result set:
