//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
      Access DB & ADO
      General SQL Server & Access Articles
      MySQL
      Other Articles
      Schema Tutorials
      Sql Server 2000
      Sql Server 2005
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
databases.aspfaq.com

ASP FAQ Tutorials :: Databases :: Schema Tutorials :: Schema: How do I show the description property of a column?


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?

 

 


Created: 1/30/2002 | Last Updated: 3/20/2005 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (107)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...