Retrieval of the Description of a Field in a SQL Server 2000 based Table
I live in Brazil, and use SQL Server 2000 SP4 with Visual Basic 5.0 SP3/6.0 SP6, with connectivity to ODBC through RDO/ADO.
I have an example table with the following structure:
Table Name: Autioneer's (translated from Portuguese)
'Field 1/3
Name: Code
DataType: int
Description: Auctioneer's Code
'Field 2/3
Name: Name
DataType: nvarchar(50)
Description: Auctioneer's Name
'Field 3/3
Name: RegNum
DataType: nvarchar(20)
Description: Auctioneer's Registration Number
I need a way to programatically extract the Fields Description Property from the table
example sintaxe:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='AUCTIONEERS'
Thanks for any help that can be offered here
Neil Ramkeerat.
Replies can be sent to neilramkeerat@hotmail.com
# 1 Re: Retrieval of the Description of a Field in a SQL Server 2000 based Table
Look at the fn_listextendedproperty function.
SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', 'AUTIONEERS', 'column', default);
Rune
# 2 Re: Retrieval of the Description of a Field in a SQL Server 2000 based Table
Thanks for the tip, I modified a bit and arrived at the following:
(01) For every field in the table, have to create an extended property like this:
sp_addextendedproperty 'Caption', 'Cdigo do Leiloeiro',
'user', dbo, 'table', Leiloeiros, 'column', Codigo
GO
sp_addextendedproperty 'Caption', 'Registro do Leiloeiro',
'user', dbo, 'table', Leiloeiros, 'column', Registro
GO
sp_addextendedproperty 'Caption', 'Nome do Leiloeiro',
'user', dbo, 'table', Leiloeiros, 'column', Nome
GO
(02) Retrieve the extended property like this:
SELECT Value
FROM ::FN_LISTEXTENDEDPROPERTY('Caption', 'User','dbo','table',
'Leiloeiros', 'column', default)
(03) Result List
'VALUE
Cdigo do Leiloeiro
Registro do Leiloeiro
Nome do Leiloeiro
(04) Conclusion: Satisfactory, but is a bit disadvantageous due to the fact that an extra job will have to be done on each table in this manner. I really wanted to make use of the DESCRIPTION PROPERTY in the table creation of the SQL Enterprise Manager
Thank you very much, Mr. Rune Bivrin
# 4 Re: Retrieval of the Description of a Field in a SQL Server 2000 based Table
I copy that..
the final statement is as such:
SELECT Value
FROM ::FN_LISTEXTENDEDPROPERTY('MS_DESCRIPTION', 'User','dbo','table',
'Leiloeiros', 'column', default)
very good and case resolved
Once again, thanks
