Can't Get Column Description from SQL Server

5 Jan 2006 12:28 PM

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

5 Jan 2006 3:00 PM

MrSmersh

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.

Show quote

"gj_williams2***@yahoo.co.uk" wrote:

> 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
>
>

5 Jan 2006 6:19 PM

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_williams2***@yahoo.co.uk> wrote in message

Show quote

news:1136464095.754342.88070@g47g2000cwa.googlegroups.com...
> 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
>

6 Jan 2006 8:41 AM

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?

6 Jan 2006 12:13 PM

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

7 Jan 2006 9:08 AM

MrSmersh

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.

Show quote

"gj_williams2***@yahoo.co.uk" wrote:

> 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
>
>

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