Show Table Structure with Description 2

[dvannoy]

I'm trying to get the table structure including the field descriptions.
There are several examples of how to do this but how do I get the field description included in the information schema ?

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'
ORDER BY ORDINAL_POSITION

Any help would be appreciated

 

[gmmastros]

Try this

Select  C.*, p.Value As FieldDescription
From    Information_Schema.Columns C
        Left Join sys.extended_properties P
          On  object_id(Table_Schema + '.' + Table_Name)  = P.major_id
          And C.Ordinal_Position = p.minor_id
          And p.name = 'MS_DESCRIPTION'
Order By TABLE_SCHEMA,
	TABLE_NAME,
	ORDINAL_POSITION

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dvannoy]

perfect, thanks