Get table, column description metadata

[David Thielen]

Hi;

We are trying to get the metadata description field for a table, view,
& column. Is there any way to get this for a table/view?

And for a column this is what we are using - but it's very slow. Is
there a faster way:

Version 8:
cmd.CommandText = "SELECT COLUMN_NAME, sysproperties.[value] FROM
INFORMATION_SCHEMA.COLUMNS LEFT OUTER JOIN " +
"sysproperties ON sysproperties.id =
OBJECT_ID(INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA + '.' + " +
"INFORMATION_SCHEMA.COLUMNS.TABLE_NAME) AND
sysproperties.smallid = INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION "
+
"AND sysproperties.name = 'MS_Description' WHERE
(OBJECTPROPERTY(OBJECT_ID(INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA " +
"+ '.' + INFORMATION_SCHEMA.COLUMNS.TABLE_NAME),
'IsMsShipped') = 0) AND (TABLE_NAME = @p1)";
cmd.Parameters.Add(MakeParam("@p1", table));

Version 9+:
cmd.CommandText = "SELECT c.name AS [Column Name], ex.value AS
Description FROM sys.columns AS c INNER JOIN " +
"sys.extended_properties AS ex ON ex.major_id = c.object_id
AND ex.minor_id = c.column_id AND ex.name = " +
"'MS_Description' WHERE (OBJECTPROPERTY(c.object_id,
'IsMsShipped') = 0) AND (OBJECT_NAME(c.object_id) = @p1)";
cmd.Parameters.Add(MakeParam("@p1", table));

??? - thanks - dave

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Zhi-Xin Ye [MSFT]]

Hello David,

Thank you for using Microsoft Managed Newsgroup Service, I'm Zhi-Xin Ye,
it's my pleasure to work with you on this issue.

To get the description metadata for a table or view, you can query the
sys.extended_properties table(SQL Server 2005/2008) or query the
sysproperties table(SQL Server 2000).

For example:

For SQL Server 2005
===============================

To display the description for tables:

SELECT t.name AS [Table Name], ex.value AS Description
FROM sys.tables AS t, sys.extended_properties AS ex
WHERE ex.major_id = t.object_id
AND ex.minor_id=0
AND ex.name = 'MS_Description'

To display the description for views:

SELECT v.name AS [View Name], ex.value AS Description
FROM sys.views AS v, sys.extended_properties AS ex
WHERE ex.major_id = v.object_id
AND ex.minor_id=0
AND ex.name = 'MS_Description'


For SQL Server 2000:
===============================
To display the description for tables:

Select t.TABLE_NAME,ep.value
FROM INFORMATION_SCHEMA.TABLES as t,dbo.sysproperties as ep
WHERE ep.id = object_id(t.TABLE_NAME)
AND t.TABLE_TYPE = 'BASE TABLE'
AND ep.type=3
AND ep.name = 'MS_Description'

To display the description for views:

Select t.TABLE_NAME,ep.value
FROM INFORMATION_SCHEMA.TABLES as t,dbo.sysproperties as ep
WHERE ep.id = object_id(t.TABLE_NAME)
AND t.TABLE_TYPE = 'VIEW'
AND ep.type=3
AND ep.name = 'MS_Description'

To speed up the query, you can use stored procedure instead. Put the SQL
statements in a stored procedure, and execute the stored procedure from C#
code.

Documents for your information:

Extended Properties in SQL Server 2000
http://msdn.microsoft.com/en-us/library/aa224810(SQL.80).aspx

Managing Metadata in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/managingm
etadata.mspx

Using Stored Procedures with a Command
http://msdn.microsoft.com/en-us/library/yy6y35y8(VS.71).aspx

If you have any questions or concerns, please don't hesitate to let me know.

Have a great day!


Sincerely,
Zhi-Xin Ye
Microsoft Managed Newsgroup Support Team

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:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[David Thielen]

thank you.

Hello David,

Thank you for using Microsoft Managed Newsgroup Service, I'm Zhi-Xin Ye,
it's my pleasure to work with you on this issue.

To get the description metadata for a table or view, you can query the
sys.extended_properties table(SQL Server 2005/2008) or query the
sysproperties table(SQL Server 2000).

For example:

For SQL Server 2005
===============================

To display the description for tables:

SELECT t.name AS [Table Name], ex.value AS Description
FROM sys.tables AS t, sys.extended_properties AS ex
WHERE ex.major_id = t.object_id
AND ex.minor_id=0
AND ex.name = 'MS_Description'

To display the description for views:

SELECT v.name AS [View Name], ex.value AS Description
FROM sys.views AS v, sys.extended_properties AS ex
WHERE ex.major_id = v.object_id
AND ex.minor_id=0
AND ex.name = 'MS_Description'


For SQL Server 2000:
===============================
To display the description for tables:

Select t.TABLE_NAME,ep.value
FROM INFORMATION_SCHEMA.TABLES as t,dbo.sysproperties as ep
WHERE ep.id = object_id(t.TABLE_NAME)
AND t.TABLE_TYPE = 'BASE TABLE'
AND ep.type=3
AND ep.name = 'MS_Description'

To display the description for views:

Select t.TABLE_NAME,ep.value
FROM INFORMATION_SCHEMA.TABLES as t,dbo.sysproperties as ep
WHERE ep.id = object_id(t.TABLE_NAME)
AND t.TABLE_TYPE = 'VIEW'
AND ep.type=3
AND ep.name = 'MS_Description'

To speed up the query, you can use stored procedure instead. Put the SQL
statements in a stored procedure, and execute the stored procedure from C#
code.

Documents for your information:

Extended Properties in SQL Server 2000
http://msdn.microsoft.com/en-us/library/aa224810(SQL.80).aspx

Managing Metadata in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/managingm
etadata.mspx

Using Stored Procedures with a Command
http://msdn.microsoft.com/en-us/library/yy6y35y8(VS.71).aspx

If you have any questions or concerns, please don't hesitate to let me know.

Have a great day!


Sincerely,
Zhi-Xin Ye
Microsoft Managed Newsgroup Support Team

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:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm