fn_listextendedproperty

fn_listextendedproperty#

Returns extended property values of database objects.

Syntax#

fn_listextendedproperty (
    { default | 'property_name' | NULL }
  , { default | 'level0_object_type' | NULL }
  , { default | 'level0_object_name' | NULL }
  , { default | 'level1_object_type' | NULL }
  , { default | 'level1_object_name' | NULL }
  , { default | 'level2_object_type' | NULL }
  , { default | 'level2_object_name' | NULL }
)

Arguments#

{ default | ‘property_name’ | NULL }

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

{ default | ‘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:

  • SCHEMA

  • NULL

{ default | ‘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 | ‘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:

  • FUNCTION

  • TABLE

  • VIEW

  • NULL

Note

Default maps to NULL and ‘default’ maps to the object type DEFAULT.

{ default | ‘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 | ‘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

  • CONSTRAINT

  • INDEX

  • NULL

{ default | ‘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. When returning extended properties on the database itself, the objtype and objname columns will be NULL.

Remarks#

If the value for property_name is NULL or default, fn_listextendedproperty returns all the properties for the specified 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 returns an empty result set.

objname is fixed as Latin1_General_CI_AI. However you can workaround this by overriding collation in comparison.

Examples#

A. Displaying extended properties on a database#

The following example displays all extended properties set on the database object itself.

SELECT objtype, objname, name, value
FROM fn_listextendedproperty(default, default, default, default, default, default, default);

B. Displaying extended properties on all columns in a table#

The following example lists extended properties for columns in the ScrapReason table. This is contained in the schema Production.

SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'Production', 'table', 'ScrapReason', 'column', default);

C. Displaying extended properties on all tables in a schema#

The following example lists extended properties for all tables contained in the Sales schema.

SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'Sales', 'table', default, NULL, NULL);

See Also#

Contents