Schema: How do I show the description property of a column?

http://databases.aspfaq.com/schema-tutorials/schema-how-do-i-show-the-description-property-of-a-column.html

Schema: How do I show the description property of a column?

SQL Server 2000 
 
You can add a description in the Enterprise Manager GUI, or you could use this code: 
 

EXEC sp_addextendedproperty      'MS_Description',      'some description',      'user',      dbo,      'table',      table_name,      'column',      column_name

 
Now, you can retrieve the values for all tables with 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

 
The commented AND in the WHERE clause is useful if you are only interested in all the columns in a single table, as opposed to all tables and all columns. 
 
And if you only want all the columns in the database that have a description, change the outer join to an 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 
 
The sysproperties table is deprecated in SQL Server 2005, so the above technique will no longer work. Thankfully, they have added a system catalog view called sys.extended_properties, which works almost the same as the sysproperties table we are already familiar with. 
 

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 the SQL Server 2000 example, you can change the code to only return columns in a single table, or all columns in the database that have a valid description. 
 
Microsoft Access 
 
In Access, you can get individual column descriptions using the following query from ASP: 
 

<%      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  %>

Related Articles

 Schema: How do I list all the indexes in a database?
 Schema: How do I list the databases on my server?
 Schema: How do I show all the primary keys in a database?
 Schema: How do I show all the triggers in a database?
 Schema: How do I show the columns for a table?
 Schema: How do I show the parameters for a function or stored procedure?
 Schema: How do I show the stored procedures in a database?
 Schema: How do I show the tables in a database?
 Schema: How do I show the user-defined functions (UDFs) in a database?
 Schema: How do I show the views in a SQL Server database?