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?
저작자 표시
신고

+ Recent posts