Metadata in sql server 2005

Hi
I add extended properties to table in sql server 2005, for example i add Caption property to every column.
In case I generate a datasource from table in sql server in visual studio 2005, Is there a way that "Caption" property in datatable to be filled from that metadata, or metadata is useless for dataset.
Every time i had to fill manually the caption property of datatable generated .

hi,

you can access those extended properties calling fn_listextendedproperty function, similar to

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.Invoice ( ID INT NOT NULL PRIMARY KEY , BillNo INT NOT NULL -- , others ) GO EXEC sp_addextendedproperty '1st property' , '1St property value' , 'user' , 'dbo' , 'table' , 'Invoice' , NULL , NULL; EXEC sp_addextendedproperty '2nd property' , '2nd property value' , 'user' , 'dbo' , 'table' , 'Invoice' , NULL , NULL; GO PRINT 'All available properties'; SELECT CONVERT(VARCHAR(20), [name]) AS [Property Name] , CONVERT(VARCHAR(20), [value]) AS [Property vCharValue] FROM ::fn_listextendedproperty( NULL , 'user' , 'dbo' , 'table' , 'Invoice' , NULL , NULL ); GO PRINT 'just the required [1st property] property'; SELECT CONVERT(VARCHAR(20), [name]) AS [Property Name] , convert(VARCHAR(20),[value]) AS [Property vCharValue] FROM ::fn_listextendedproperty( '1st property' , 'user' , 'dbo' , 'table' , 'Invoice' , NULL , NULL ); GO DROP TABLE dbo.Invoice; --< All available properties Property Name Property vCharValue -- -- 1st property 1St property value 2nd property 2nd property value just the required [1st property] property Property Name Property vCharValue -- -- 1st property 1St property value

so, assuming you are interested in the Invoice management, you have to query for all the properties of the dbo.Invoice table columns...

this can obviously fill a dataset or a datareader you can then consume to map as desired the returned values so you could assume the "1st property" is the caption of the corresponding UI control and you can set it accordingly..

BTW, to manage those properties outside the Microsoft tools you have to resort on http://msdn2.microsoft.com/en-us/library/aa174648(SQL.80).aspx and http://msdn2.microsoft.com/en-us/library/ms190243.aspx

regards|||Thank you for your reply.
I mean , what is the extended properties that may be used automatically by visual studio 2005.
For example , Can the "Caption " property of datatable created in vs 2005 , be set automatically using extended properties?
This property is useful, because form wizard creates label with that "caption" value(when i set it at design time), and textbox bound to column.

|||

hi,

no, you have to deal with that "by hand"...

regards