sys.fn_listextendedproperty (Transact-SQL)

Article
02/28/2023

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

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:

ASSEMBLY
CONTRACT
EVENT NOTIFICATION
FILEGROUP
MESSAGE TYPE
PARTITION FUNCTION
PARTITION SCHEME
REMOTE SERVICE BINDING
ROUTE
SCHEMA
SERVICE
TRIGGER
TYPE
USER
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
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)
NULL. Valid inputs for level2_object_type are:
COLUMN
CONSTRAINT
EVENT NOTIFICATION
INDEX
PARAMETER
TRIGGER
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.

SELECT o.[object_id] AS 'table_id', o.[name] 'table_name',  
0 AS 'column_order', NULL AS 'column_name', NULL AS 'column_datatype',  
NULL AS 'column_length', Cast(e.value AS varchar(500)) AS 'column_description'  
FROM AdventureWorks.sys.objects AS o  
LEFT JOIN sys.fn_listextendedproperty(N'MS_Description', N'user',N'HumanResources',N'table', N'Employee', null, default) AS e  
    ON o.name = e.objname COLLATE SQL_Latin1_General_CP1_CI_AS  
WHERE o.name = 'Employee';

Permissions

Permissions to list extended properties of objects vary by object type.

Examples

A. Displaying extended properties on a database

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

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

Here's the result set.

objtype objname name value

--------- --------- ----------- ----------------------------

NULL NULL MS_Description AdventureWorks2008 Sample OLTP Database

(1 row(s) affected)

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.

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

Here's the result set.

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

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

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

Share via