mssql 2005에서 테이블, 칼럼 정보 가져오기

http://sqler.pe.kr/web_board/view_list.asp?part=myboard1&id=84642
http://sqler.pe.kr/web_board/view_list.asp?part=myboard1&id=73253
나와 같은 고민의 흔적

http://databases.aspfaq.com/schema-tutorials/schema-how-do-i-show-the-description-property-of-a-column.html
해결방법은 여기서 찾았다.
2000, 2005에 Access까지 친절하게 설명해 놓았다.

자주 쓰일 것 같으니 잠시 옮겨두자.

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