Include column Description with list of table properties

[L Mehl]

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