{ 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 ASSEMBLY, CONTRACT, EVENT NOTIFICATION, FILEGROUP, MESSAGE TYPE, PARTITION FUNCTION, PARTITION SCHEME, REMOTE SERVICE BINDING, ROUTE, SCHEMA, SERVICE, TRIGGER, TYPE, USER, and NULL.

Important:
USER and TYPE as level-0 types will be removed in a future version of SQL Server. Avoid using these features in new development work, and plan to modify applications that currently use these features. Use SCHEMA as the level 0 type instead of USER. For TYPE, use SCHEMA as the level 0 type and TYPE as the level 1 type.

{ 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 AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, SYNONYM, TABLE, TYPE, VIEW, XML SCHEMA COLLECTION, and 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 DEFAULT, default (maps to NULL), and NULL. Valid inputs for level2_object_type are COLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER, TRIGGER, and 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.

This is the format of the tables returned by fn_listextendedproperty.

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.

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.

Permissions to list extended properties of objects vary by object type. For a complete list of permissions, see Using Extended Properties on Database Objects.

A. Displaying extended properties on a database

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

objtype    objname     name            value
---------  ---------   -----------     ----------------------------
NULL       NULL        MS_Description  AdventureWorks Sample OLTP Database

(1 row(s) affected)

B. Displaying extended properties on all columns in a table

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

objtype objname      name            value   
------- -----------  -------------   ------------------------
COLUMN ScrapReasonID MS_Description  Primary key for ScrapReason records.
COLUMN Name          MS_Description  Failure description.
COLUMN ModifiedDate  MS_Description  Date the record was last updated.

(3 row(s) affected)

C. Displaying extended properties on all tables in a schema

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

Reference

Other Resources

Help and Information