[팁] MSSQL 2005에서 COMMENT 보기

/**=====================================================================================================================
 * COLUMN COMMNET SELECT QUERY
 *=====================================================================================================================*/
SELECT
      tb.name, -- TABLE NAME
      c.name, --FIELD
      t.name AS typename, -- TYPE
      c.max_length AS length, -- SIZE
      c.is_nullable, -- NULL / NOT NULL
      c.is_identity, -- Identity Yes/No
      CAST(p.[value] AS varchar) AS cmt --FIELD COMMENT
FROM sys.columns c
     INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
     INNER JOIN sys.objects tb ON tb.object_id = c.object_id
     INNER JOIN sys.schemas u ON u.schema_id = tb.schema_id
     LEFT OUTER JOIN sys.extended_properties p ON p.major_id = c.object_id
          AND p.minor_id = c.column_id
          AND p.class = 1
          AND p.name = 'MS_Description'
WHERE tb.name = 'FM_EXP_TOOL' AND u.name = 'dbo'
ORDER BY c.column_id

/**=====================================================================================================================
 * TABLE COMMNET SELECT QUERY
 *=====================================================================================================================*/
SELECT
      o.name,
      u.name AS owner,
      o.type,
      CAST(p.[value] AS varchar) AS cmt

FROM sys.tables o INNER JOIN sys.schemas u ON u.schema_id = o.schema_id
LEFT OUTER JOIN sys.extended_properties p ON p.major_id = o.object_id AND p.minor_id = 0 AND p.name = 'MS_Description'
WHERE o.name = 'FM_EXP_TOOL' AND u.name = 'dbo'

/**=====================================================================================================================
 * PROCEDURE COMMNET SELECT QUERY
 *=====================================================================================================================*/
SELECT
      o.name,
      u.name AS owner,
      o.type,
      CAST(p.[value] AS varchar) AS cmt
FROM sys.objects o INNER JOIN sys.schemas u ON u.schema_id = o.schema_id
LEFT OUTER JOIN sys.extended_properties p ON p.major_id = o.object_id
AND p.minor_id = 0 AND p.name = 'MS_Description'
WHERE o.type IN ('P', 'RF') AND o.name = 'CODE_ACTION' AND u.name = 'dbo'