SQL Server 2000
You can add field descriptions in the Enterprise Manager, or use the followingCode:
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 By
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 instructions, you can change out join to inner join.
Select
[ 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 By
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
% >
