Can't Get Column Description from SQL Server - HELP!

[gj_williams2000]

Hi,

I have an SQL Server2000 database and I am trying to obtain column
descriptions programmatically in vb.net (VS2003).

If I edit the design of one of my tables in Enterprise Manager I can
add a description to each column but I have so far been unable to
access this information from vb. I read somewhere that you can use the
GetOleDbSchemaTable method of an OleDbConnection object but when I do
this the description column is always null. I have obtained other
table information via this method, such as whether the table has an
autonumber, without any problems so it seems to me that the description
isn't available for some reason?

Can anyone help?

Cheers

Gareth

 

[Guest]

As far as I see the stored procedures sp_help and sp_helpcolumns do not
return this information either. Also the INFORMATION_SCHEMA views do not
return this info.

So you could do a query on the tables/views that store that info but this
solution is not 100% portable since Microsoft does not guarantee that will
not change their structure. But do not know where is it, in syscolumns this
description is not, maybe is syscomments but I doubt it.

 

[William \(Bill\) Vaughn]

The 2.0 Framework includes GetSchema methods that can fully enumerate the
structure from most providers.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[gj_williams2000]

Thats all well and good but not really much use to me.

Thanks for your replies but I can't find the description info in any
system tables or sp's.

The database must be storing the description somewhere otherwise
enterprise manager wouldn't read it so how does enterprise manager do
it?

 

[gj_williams2000]

I have found out how to get the description. Here is a vb.net function
to do it if anyone is interested:

Public Shared Function GetColumnDescription(ByVal TableName As String,
ByVal ColumnName As String) As String
Dim SQL As String = _
"SELECT sysproperties.[value] AS COLUMN_DESCRIPTION " &
_
"FROM syscolumns INNER JOIN systypes " & _
"ON syscolumns.xtype = systypes.xtype " & _
"LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id
" & _
"LEFT OUTER JOIN sysproperties ON " & _
"(sysproperties.smallid = syscolumns.colid " & _
"AND sysproperties.id = syscolumns.id) " & _
"LEFT OUTER JOIN syscomments ON syscolumns.cdefault =
syscomments.id " & _
"WHERE syscolumns.id IN " & _
"(SELECT id FROM SYSOBJECTS WHERE xtype = 'U') " & _
"AND (systypes.name <> 'sysname') " & _
"AND (UPPER(Sysobjects.name)='" & TableName.ToUpper() &
"') " & _
"AND (UPPER(syscolumns.name)='" & ColumnName.ToUpper()
& "') "

Dim cm0 As New OleDbCommand

Try
cm0.Connection = New OleDbConnection(psConnectionString)
cm0.Connection.Open()

cm0.CommandText = SQL
Dim o As Object = cm0.ExecuteScalar()

If o Is Nothing Then
Return ""
Else
Return o.ToString()
End If
Catch ex As Exception
MessageBox.Show("An error occurred while fetching column
description with the following message: " & ex.Message,
frmMain.psProductName, MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
If Not cm0 Is Nothing Then
If Not cm0.Connection Is Nothing Then
If cm0.Connection.State <> ConnectionState.Closed
Then
cm0.Connection.Close()
End If
cm0.Connection.Dispose()
End If
cm0.Dispose()
End If
End Try
End Function

 

[Guest]

Ok so you find it.

Please beware there is no guarantee that this tables will remain as such in
future releases of SQL server.

So to access the extended properties use “fn_listextendedproperty†you will
find it in help. I believe that will get you the description and is upgrade
safe.