Column and table descriptions are stored as extended properties, you can read more about extended properties in BOL. Microsoft uses a convention of naming them MS_Description, they are the ones that show in Enterprise Manager. You can declare your own extended properties and use them for data validation or other things.
You can create extended properties with the sp_addextendedproperty stored procedure and you can retrieve them by calling the fn_listextendedproperty() function. Here is how you get all column descriptions for the Orders table:
SELECT * FROM ::fn_listextendedproperty('MS_Description', 'user', 'dbo',
'table', 'Orders', 'column', null)
More examples:
USE NORTHWIND
GO
-- add table comment
DECLARE @v sql_variant
SET @v = N'This table holds orders'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo',
N'table', N'Orders', NULL, NULL
GO
-- add column comment
DECLARE @v sql_variant
SET @v = N'OrderID is auto-generated'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo',
N'table', N'Orders', N'column', N'OrderID'
GO
-- get table description
SELECT * FROM ::fn_listextendedproperty('MS_Description', 'user', 'dbo',
'table', 'Orders', null, null)
-- get OrderID column description
SELECT * FROM ::fn_listextendedproperty('MS_Description', 'user', 'dbo',
'table', 'Orders', 'column', 'OrderID')
-- get descriptions for all columns in the table
SELECT * FROM ::fn_listextendedproperty('MS_Description', 'user', 'dbo',
'table', 'Orders', 'column', null)
-- get all table properties
SELECT * FROM ::fn_listextendedproperty(null, 'user', 'dbo',
'table', 'Orders', null, null)
-- get all column properties
SELECT * FROM ::fn_listextendedproperty(null, 'user', 'dbo',
'table', 'Orders', 'column', null)