Home > SQL Server > Get the extended property value of database objects using fn_listextendedproperty – SQL Server

Get the extended property value of database objects using fn_listextendedproperty – SQL Server


fn_listextendedproperty() : This function returns the extended property value of database objects such as (table, column, index, schema and etc). Using this function we can retrieve single property value of an object or all property values of any object type. We have to choose proper function arguments to get the required value. There are 3 types of value we can use in this function argument. default, NULL and property name or object name or object type.

This function returns output in a table

pic1

Function 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 }
)

Argument details

Property Name : This is the name of the user defined property name.

Level 0 (object type, object name)

Valid Object type : ASSEMBLY, CONTRACT, EVENT NOTIFICATION, FILEGROUP, MESSAGE TYPE, PARTITION FUNCTION, PARTITION SCHEME, REMOTE SERVICE BINDING, ROUTE, SCHEMA, SERVICE, TRIGGER, TYPE, USER, and NULL. Note: This argument will be removed from the future version of SQL Server. So, please avoid using in the new development.

Object name: User defined actual object name or NULL or default

Level 1 (object type, object name)

Valid object type: AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, SYNONYM, TABLE, TYPE, VIEW, XML SCHEMA COLLECTION, and NULL.

object name: User defined actual object name or NULL or default

Level 2 (object type, object name)

Valid object type:  COLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER, TRIGGER, and NULL.

object name: User defined actual object name or NULL or default

Experiment

I am using [AdventureWorks2012] database for this experiment.

1. If all values in the function argument is default the it returns the current database extended property name.

select * from fn_listextendedproperty(default,default,default,default,default,default,default)

pic2

2 I would like to get all extended property value of table objects in schema dbo in my database. Here is the query

select * from fn_listextendedproperty(default,’schema’,’dbo’,’table’,default,default,default)

pic3

Now, it is your turn. You can experiment this function in your machine. I will talk about a set of functions that are used to add, delete, update the extended property value in my next blog post.

Advertisements
  1. srinivasan
    April 19, 2013 at 11:24 pm

    Its great thing that u sharing ur knowledge with others. Please carry on. Thanks friend

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: