Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Accessing the Description property of a field Expand / Collapse
Author
Message
Posted Wednesday, April 21, 2004 9:06 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 20, 2013 5:39 PM
Points: 207, Visits: 197

In Enterprise Manager, in table design, you can enter a Description.

Does anyone know a query that will return the columns of a of a table and show the 'Description' property of the fields? 




Post #112335
Posted Wednesday, April 21, 2004 11:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:46 AM
Points: 1,422, Visits: 1,883

these descriptions are stored in the sysproperties system table (don't know if they are also stored in any INFORMATION_SCHEMA view) along with the object id of the table...

this query should (hopefully) return the column name and the description (if any):

DECLARE @TableName Varchar(100)

SET @TableName = 'xxxxxxxx'

SELECT
 SC.name,
 SP.value
FROM syscolumns SC LEFT OUTER JOIN sysproperties SP ON
SP.id = SC.id
WHERE SC.id = object_id(@TableName)

 

 

 




Post #112379
Posted Thursday, April 22, 2004 2:46 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, June 20, 2012 5:02 AM
Points: 530, Visits: 945

I've found a small change should eliminate repeating rows:

DECLARE @TableName Varchar(100)

SET @TableName = 'ASection'

SELECT
 SC.name,
 SP.value
FROM syscolumns SC LEFT OUTER JOIN sysproperties SP ON
SP.id = SC.id
and SC.colid = SP.smallid
WHERE SC.id = object_id(@TableName)

Post #112484
Posted Thursday, April 22, 2004 6:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 20, 2013 5:39 PM
Points: 207, Visits: 197
Thanks guys.  This is exactly what I needed.


Post #112521
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse