Obtain field descriptions in SQL Server

How to obtain field descriptions from SQL Server

SQL Server 2000

You can add a field description in the Enterprise Manager or use the following code: EXEC sp_addextendedproperty
'Ms _ description ',
'Some description ',
'User ',
Dbo,
'Table ',
Table_name,
'Column ',
Column_name

Now you can get the field description through the following code: SELECT
[Table Name] = I _s.TABLE_NAME,
[Column Name] = I _s.COLUMN_NAME,
[Description] = s. value
FROM
INFORMATION_SCHEMA.COLUMNS I _s
LEFT OUTER JOIN
Sysproperties s
ON
S. id = OBJECT_ID (I _s.TABLE_SCHEMA + '.' + I _s.TABLE_NAME)
AND s. smallid = I _s.ORDINAL_POSITION
AND s. name = 'Ms _ description'
WHERE
OBJECTPROPERTY (OBJECT_ID (I _s.TABLE_SCHEMA + '.' + I _s.TABLE_NAME), 'ismsshipped ') = 0
-- AND I _s.TABLE_NAME = 'table _ name'
ORDER
I _s.TABLE_NAME, I _s.ORDINAL_POSITION

If you only care about a table, the comment section in the preceding TSQL is very helpful to you. In turn, it will give you all the fields in all the tables.

If you only need all tables with descriptions, you can change out join to inner joinSELECT.
[Table Name] = I _s.TABLE_NAME,
[Column Name] = I _s.COLUMN_NAME,
[Description] = s. value
FROM
INFORMATION_SCHEMA.COLUMNS I _s
INNER JOIN
Sysproperties s
ON
S. id = OBJECT_ID (I _s.TABLE_SCHEMA + '.' + I _s.TABLE_NAME)
AND s. smallid = I _s.ORDINAL_POSITION
AND s. name = 'Ms _ description'
WHERE
OBJECTPROPERTY (OBJECT_ID (I _s.TABLE_SCHEMA + '.' + I _s.TABLE_NAME), 'ismsshipped ') = 0
ORDER
I _s.TABLE_NAME, I _s.ORDINAL_POSITION

SQL Server 2005

In SQL Server 2005, The sysproperties table has been deprecated, so none of the above Code can be used. Fortunately, they added a system table to sys. extended_properties, which is basically similar to sysproperties.
SELECT
[Table Name] = OBJECT_NAME (c. object_id ),
[Column Name] = c. name,
[Description] = ex. value
FROM
Sys. columns c
LEFT OUTER JOIN
Sys. extended_properties ex
ON
Ex. major_id = c. object_id
AND ex. minor_id = c. column_id
AND ex. name = 'Ms _ description'
WHERE
OBJECTPROPERTY (c. object_id, 'ismsshipped ') = 0
-- AND OBJECT_NAME (c. object_id) = 'your _ table'
ORDER
BY OBJECT_NAME (c. object_id), c. column_id

Like SQL Server 2000, you can use the comments section to return a table.
Microsoft Access
In Access, you can use the following ASP code to get a description of a field.
<%
On error resume next
Set Catalog = CreateObject ("ADOX. Catalog ")
Catalog. ActiveConnection = "Provider = Microsoft. Jet. OLEDB.4.0 ;"&_
"Data Source = <path >\< file>. mdb"

Dsc = Catalog. Tables ("table_name"). Columns ("column_name"). Properties ("Description"). Value

If err. number <> 0 then
Response. Write "& lt;" & err. description & "& gt ;"
Else
Response. Write "Description =" & dsc
End if
Set Catalog = nothing
%>