Aamir said:
Hi,
Can I get the column's description in my code, for each columns in
the table. 'Column description' is set at the table design time.
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
Then, check the ObjectType for each row: U is table, V is view and P
is proc (yeah, you start to wonder why table isn't T but thats
sqlserver
). Then, you traverse the Column names which represent
table fields, view fields or proc parameters and grab the PropertyValue
value of the row for the description
FB
--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website:
http://www.llblgen.com
My .NET blog:
http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
