how to get column description of a table in sql server 2000 programmatically

Question

hello every body,

 

I have created a table and given columns descriptions( while in design view of Create table) in sql server 2000. i want to get that columns descriptions in vb 6.

 

Have any one done it before or any idea about it?

 

Thank you very much in anticipation

 

Mian Imran Iqbal

 

Answer 1

From BOL

 

Examples

This example lists all extended properties for the database.

SELECT   *
FROM   ::fn_listextendedproperty(NULL, NULL, NULL, NULL, NULL, NULL, NULL)

-Or-

SELECT   *
FROM   ::fn_listextendedproperty(default, default, default, default, default, default, default)

This example lists all extended properties for all columns in table 'T1.'

CREATE   table T1 (id int , name char (20))

EXEC   sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo, 'table', 'T1', 'column', id

EXEC   sp_addextendedproperty 'caption', 'Employee Name', 'user', dbo, 'table', 'T1', 'column', name

SELECT   *
FROM   ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'T1', 'column', default)

Here

 

 

Madhu

 

Answer 2

Here are a few examples:

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.database.extendedproperties.aspx

 

I think this has everything you need in it.

Answer 3

 

SQLSERVER 2000:

SELECT    sysobjects.Name AS ObjectName,
            sysobjects.xtype AS ObjectType,
            user_name(sysobjects.uid) AS SchemaOwner,
            sysproperties.name AS PropertyName,
            sysproperties.value AS PropertyValue,
            syscolumns.name AS ColumnName,
            syscolumns.colid AS Ordinal
FROM    sysobjects INNER JOIN sysproperties
            ON sysobjects.id = sysproperties.id
            LEFT JOIN syscolumns
            ON sysproperties.smallid = syscolumns.colid
            AND sysproperties.id = syscolumns.id
ORDER BY SchemaOwner, ObjectName, ObjectType, Ordinal

SQLSERVER 2005:
SELECT        o.Name AS ObjectName,
            o.type AS ObjectType,
            s.name AS SchemaOwner,
            ep.name AS PropertyName,
            ep.value AS PropertyValue,
            c.name AS ColumnName,
            c.colid AS Ordinal
FROM        sys.objects o INNER JOIN sys.extended_properties ep
            ON o.object_id = ep.major_id
            INNER JOIN sys.schemas s
            ON o.schema_id = s.schema_id
            LEFT JOIN syscolumns c
            ON ep.minor_id = c.colid
            AND ep.major_id = c.id
WHERE        o.type IN ('V', 'U', 'P')
ORDER BY    SchemaOwner,ObjectName, ObjectType, Ordinal

Answer 4

Thanks this helped me too!