How can I retrieve Description and other column information?

Hi, all..
I want to know the query to retrieve Column information that we can see from table Design view of Enterprise manager, such as Column name, Pk or not, FK table, Data Type, Null or not, Description(Specially Descrition).

Is there any sp for this or any query for this?

Thank you all...type

sp_help tablename|||select * from INFORMATION_SCHEMA.TABLES
select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE

See BOL for more information about schema.|||You can also use
INFORMATION_SCHEMA.columns
cheers
-ss|||I too would like to get at the Description and I tried all of the above but Description doesn't seem to be there. I looked at all the INFORMATION_SCHEMA views and found nothing. I checked the system tables for the DB in question and found nothing. Could the Description be something internal to EM that isn't available to clients?|||What do you mean by description?|||I assume the Column Description you can enter in EM when designing a table. It's the first item on the Columns tab.|||OK...I usually maitain my data dictionary separatley...

You need to look up

fn_listextendedproperty

Never really used it....|||I assume the Column Description you can enter in EM when designing a table. It's the first item on the Columns tab.

Check this table:

select * from sysproperties|||Yup... Works fine.

Thanks Brett..|||select * from sysproperties

Works as well but may take extra coding to figure out the id and smallid so the data for the correct column can be identified.

Thanks...|||It works!!! Assuming a Table name of 'KB' and a column named 'Title' this gets the description.

declare @.tbid integer
declare @.colid integer

Select @.tbid=id from sysobjects where xtype='u' and name='KB'
Select @.colid = colid from syscolumns Where id=@.tbid And name='Title'
select value from sysproperties where id=@.tbid and smallid=@.colid and name='MS_Description'

Thanks snail for the pointer to sysProperties.|||Thanks all..
I think we have to know sysproperties table joined with which table..|||Can't help with the JOIN. There are three tables here and I haven't figured out how to JOIN two yet! :)|||Now I am finishing ...
This works! the following three tables joined gives what I asked.
I hope this helps all...
Thank you all for considering

Use Northwind
SELECT sysobjects.name AS [Table], syscolumns.name AS [Column], sysproperties.[value] AS Description
FROM (sysobjects INNER JOIN syscolumns
ON syscolumns.id = sysobjects.id) LEFT JOIN sysproperties
ON sysproperties.smallid = syscolumns.colid AND
sysproperties.id = syscolumns.id
where sysobjects.name = 'Orders'

-- PS
sysobjects.name : table Name (WHERE sysobjects.xtype = 'U')
sysobjects.id : table ID
sysproperties.smallid : Column id
sysproperties.[value] : Description

You can add following
WHERE sysobjects.xtype = 'U' xtype='U' means only select Table object