SQL Server

Using the System Function fn_listextendedproperty of SQL Server 2005 for .NET Applications: Part II

I think all of us , developing some  .NET application (it doesn't matter ASP.Net or Windows forms)  with SQL Server as database, sooner or later face the next task.

 

You have some grid ( DataGrid or something like that ) which should be filled dynamically: or Employee table should be "loaded" , or - Cities table, or - Countries table,  and so on. Of course every time you have to update "header text" of each column. One more situation. In order to edit (update, insert ...) ten  tables ( may be much more - one hundred tables !)  you have to build only one form. All these tables have the same design , for example : the first column has data type "int" and the second  -  "char(50)" . Thus, to edit these tables there should be at least two textBoxes and two labels on your form and every time with changing of edited table you have to change "text" of the labels. It is clear that in these situations you  must use "something" to fill and/or change text  properties (or column headers) automatically according to change of the table. And this "something" is the system function fn_listextendedproperty .

 

In this article, divided into two parts, I will compare the system function fn_listextendedproperty of SQL Server 2005 with the similar function of SQL Server 2000 (Part 1), describe using the function for .NET applications (Part 2). The examples are written using Transact-SQL and C#.

 

The system function fn_listextendedproperty  returns extended property values of database objects and has the following syntax (see also Table 1.):

 

fn_listextendedproperty  (arg_name, arg0_type, arg0_name, arg1_type, arg1_name,  

arg2_type, arg2_name).

 

Table 1. Argument symbols and arguments.

 

Argument symbolArgument
arg_namedefault | 'property_name' | NULL
arg0_typedefault | 'level0_object_type' | NULL
arg0_namedefault | 'level0_object_name' | NULL
arg1_typedefault | 'level1_object_type' | NULL
arg1_namedefault | 'level1_object_name' | NULL
arg2_typedefault | 'level2_object_type' | NULL
arg2_namedefault | 'level2_object_name' | NULL

The comparison of the function of SQL Server 2000 edition with SQL Server 2005 edition are given in table 2.

 

Table 2. Valid inputs for arguments (SQL Server 2000 / 2005).

 

 

ArgumentValid inputs [2000]Valid inputs [2005]
arg_namedefault, NULL, property namedefault, NULL, property name
arg0_typeUSER, TYPE, default, NULLASSEMBLY, CONTRACT, EVENT NOTIFICATION, FILEGROUP, MASSAGE TYPE, PARTITION FUNCTION, PARTITION SCEME, REMOTE SERVICE BINDING, ROUTE, SCEMA, SERVICE, TRIGGER, TYPE, USER, NULL
arg0_namedefault, NULL, object namedefault, NULL, object name
arg1_typeTABLE,VIEW, PROCEDURE, FUNCTION, DEFAULT, RULE, NULLAGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, SYNONYM, TABLE, TYPE, VIEW, XML SCHEMA COLLECTION, NULL
arg1_namedefault, NULL, object namedefault, NULL, object name
arg2_typeCOLUMN, CONSTRAINT, INDEX, PARAMETER, TRIGGER, NULL, DEFAULTCOLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER, TRIGGER, NULL, DEFAULT
arg2_namedefault, NULL, object namedefault, NULL, object name

Microsoft asks to pay attention that USER and TYPE as level-0 types will be removed in a future version of SQL Server. We have to avoid using these features in new development work, and plan to modify applications that currently use these features. SCEMA as the level-0 type must be used instead of USER .

 

Using the  AdventureWorks  database (SQL Server 2005) you can test "work" of function with various arguments :

 

--query 1

--displaying extended properties on the database itself

select objtype,objname,[name],[value]

from fn_listextendedproperty (null,null,null,null,null,null,null)

 

The result is shown in figure 1.

Fig_1.gif

Figure 1.

 

--query 2

--displaying extended properties on all schemas

select objtype,objname,[name],[value]

from fn_listextendedproperty (null,'schema',null,null,null,null,null)

 

The result is shown in figure 2.

Fig_2.gif

Figure 2.

 

--query 3

--displaying extended properties on the HumanResources schema

select objtype,objname,[name],[value]

from fn_listextendedproperty (null,'schema','HumanResources',null,null,null,null)

 

The result is shown in figure 3.

Fig_3.gif

Figure 3.

 

--query 4

--displaying extended properties on all tables

--in the HumanResources schema

select objtype,objname,[name],[value]

from fn_listextendedproperty (null,'schema','HumanResources','TABLE',null,null,null)

 

The result is shown in figure 4.

Fig_4.gif

Figure 4.

 

--query 5

--displaying extended properties on the Employee table

--in the HumanResources schema

select objtype,objname,[name],[value]

from fn_listextendedproperty (null,'schema','HumanResources','TABLE','Employee',null,null)

 

The result is shown in figure 5.

Figure 5.

 Fig_5.gif

--query 6

--displaying extended properties on all columns of the Employee table 

--in the HumanResources schema

select objtype,objname,[name],[value]

from fn_listextendedproperty (null,'schema','HumanResources','TABLE','Employee','COLUMN',null)

 

The result is shown in figure 6.

Fig_6.gif

Figure 6.

 --query 7

--displaying extended properties on the EmployeeID column 

--of the Employee table in the HumanResources schema

select objtype,objname,[name],[value]

from fn_listextendedproperty (null,'schema','HumanResources','TABLE','Employee','COLUMN','EmployeeID')

 

The result is shown in figure 7.

Fig_7.gif

Figure 7.

 

--query 8

--displaying extended properties on the MaritalStatus column  

--of the Employee table in the HumanResources schema

select objtype,objname,[name],[value]

from fn_listextendedproperty (null,'schema','HumanResources','TABLE','Employee','COLUMN','MaritalStatus')

 

The result is shown in figure 8.

Fig_8.gif

Figure 8.

 

OK ! I think, that we have worked well enough. Let's drink a cup of coffee before we shall pass to the second part .

 

Good luck in programming !

Up Next
    Ebook Download
    View all
    Learn
    View all
    Membership not found