Re: How do I get all column descriptions from a schema.table?
- From: v-micsun@xxxxxxxxxxxxxxxxxxxx (Lingzhi Sun [MSFT])
- Date: Tue, 07 Jul 2009 04:13:21 GMT
Hi David,
The error is thrown because except column description, the
HumanResources.Employee data table in Adventureworks database has index
description as well. So the subquery will return more than one value.
To avoid this issue and only retrieve the column description, we can filter
the data by (class) = 1, because the column class is equal to 1 in the
sys.entended_properties
(http://msdn.microsoft.com/en-us/library/ms177541.aspx).
======================================================
SELECT sys.columns.name AS ColumnName,
sys.types.name AS ColumnType,
(SELECT value FROM sys.extended_properties
WHERE (major_id = sys.columns.object_id) AND
(minor_id = sys.columns.column_id) AND
((class) = 1)) AS ColumnDescription
FROM sys.columns
INNER JOIN sys.tables ON sys.columns.object_id = sys.tables.object_id
INNER JOIN sys.types ON sys.columns.user_type_id = sys.types.user_type_id
INNER JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id
WHERE (sys.tables.name = 'Employee') AND (sys.schemas.name =
'HumanResources')
======================================================
Besides, to retrieve the other descriptions, we can filter the by setting
the (class) value, e.g. Database = 0, Schema = 3, index = 7 and etc.
If you have any questions, please feel free to let me know.
Have a nice day!
Best Regards,
Lingzhi Sun
Microsoft Online Community Support
=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
msdnmg@xxxxxxxxxxxxxx
This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================
.
- References:
- Re: How do I get all column descriptions from a schema.table?
- From: David Thielen
- Re: How do I get all column descriptions from a schema.table?
- From: Lingzhi Sun [MSFT]
- Re: How do I get all column descriptions from a schema.table?
- From: David Thielen
- Re: How do I get all column descriptions from a schema.table?
- Prev by Date: Re: How do I get all column descriptions from a schema.table?
- Next by Date: RE: EF: selecting all the relations of a relation
- Previous by thread: Re: How do I get all column descriptions from a schema.table?
- Next by thread: Re: How do I get all FK:PK mappings with the schema info?
- Index(es):
Relevant Pages
|
Loading
