By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,841 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Include column Description with list of table properties

P: n/a
Hello --

I am not yet an advanced SQL code writer.

Can someone help me add the extended property 'Description' to a list of
columns resulting from this code?

SELECT
SUBSTRING(table_name,1,20) AS "Table",
SUBSTRING(column_name,1,30) AS "Column",
SUBSTRING(data_type,1,13) AS "DataType",
character_maximum_length AS "Length",
numeric_precision AS "Precision",
numeric_scale AS "Scale"
FROM information_schema.columns
WHERE
table_schema = 'DBO'
ORDER BY table_name, ordinal_position
The following code

SELECT objname AS ColName,
value AS Description
FROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table',
'tablename', 'column', null)

results in:
ColName Description
....name of column description of column
for one table at a time

I want to include the value of 'Description' to the first set of code.

Would it be simpler to use sysobjects and sysproperties for the Description?
If so, can someone suggest how?
This code gives me the table and comment for a column, but I can't figure
out how to add the column to which the comment belongs.

SELECT obj.name AS [Table], prop.value AS [Description]
FROM sysobjects obj JOIN sysproperties prop ON obj.id = prop.id

Thanks for any help.

Larry Mehl


---

Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.806 / Virus Database: 548 - Release Date: 12/5/2004
Jul 23 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.