[oracle] 컬럼정보 조회

SELECT

 A.TABLE_NAME, NVL(A.COMMENTS, '') AS T_CON,

 B.COLUMN_NAME,  B.DATA_PRECISION, B.DATA_SCALE,

 DECODE(B.NULLABLE, 'Y', 'NULL', 'N', 'NOT NULL') AS NOT_NULL,

 NVL(C.COMMENTS, '') AS COL_CON,

 B.DATA_TYPE, B.DATA_LENGTH, B.COLUMN_ID

 , (

     SELECT DECODE(UC.CONSTRAINT_TYPE, 'P',POSITION , '0') PRIMARY_KEY

     FROM USER_CONS_COLUMNS UCC, USER_CONSTRAINTS UC

     WHERE UCC.TABLE_NAME = UC.TABLE_NAME

     AND UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME

     AND UC.CONSTRAINT_TYPE = 'P'

     AND UCC.TABLE_NAME = A.TABLE_NAME

     AND UCC.COLUMN_NAME = B.COLUMN_NAME

 ) PRIMARY_KEY

 FROM

 USER_TAB_COMMENTS A, USER_TAB_COLUMNS B, USER_COL_COMMENTS C

 WHERE

 A.TABLE_NAME = B.TABLE_NAME

 AND A.TABLE_NAME = C.TABLE_NAME

 AND B.COLUMN_NAME = C.COLUMN_NAME

 AND A.TABLE_NAME = 'EMP'

 ORDER BY  A.TABLE_NAME, B.COLUMN_ID

[MSSQL] 컬럼 코멘트 조회 쿼리

 SELECT * FROM (
select  b.name ColumnName, c.name TypeName, b.length ColumnLength
, Case b.IsNullable when 1 then '' else 'NN' end Nullable
, Case When g.name is null Then '' Else 'PK' End PKwithCidx
, IsNull(d.value,'') Caption
from EVENT..sysobjects a
JOIN EVENT..syscolumns b ON (a.id = b.id and a.type='U' and a.status > 0 and a.name='TBL_MAIL_INFO')
JOIN EVENT..systypes c ON (b.xtype = c.xtype and c.name<>'sysname')
LEFT JOIN EVENT..sysproperties d ON  (b.id=d.id and b.colid = d.smallid)
LEFT JOIN (select name,e.id,colid from EVENT..sysindexes e join EVENT..sysindexkeys f on e.id=f.id
where e.id > 10000000 and e.indid=1 and f.indid=1) g ON (g.id=b.id and g.colid=b.colid)
--order by a.name, colorder
) X
WHERE ColumnName = 'MAIL_SEQ'

SQLServer 2008 버전에선 아래와 같이

SELECT 

*

FROM INFORMATION_SCHEMA.COLUMNS T1 

LEFT OUTER JOIN ( 

 SELECT 

 T.NAME TABLE_NAME, TD.VALUE TABLE_DESC 

 , C.NAME COLUMN_NAME, CD.VALUE COLUMN_DESC 

 FROM SYSOBJECTS T 

 INNER JOIN SYSUSERS U ON U.UID = T.UID 

 LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES TD ON TD.MAJOR_ID = T.ID AND TD.MINOR_ID = 0 AND TD.NAME = 'MS_Description' 

 INNER JOIN SYSCOLUMNS C ON C.ID = T.ID  

 LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES CD ON CD.MAJOR_ID = C.ID AND CD.MINOR_ID = C.COLID AND CD.NAME = 'MS_Description' 

 WHERE T.TYPE = 'u' 

) T2 ON T2.TABLE_NAME=T1.TABLE_NAME AND T2.COLUMN_NAME=T1.COLUMN_NAME 

WHERE T1.TABLE_NAME = ?

ORDER BY ORDINAL_POSITION 

[MSSQL] 테이블 컬럼조회 쿼리

-- 테이블 리스트 

SELECT * FROM INFORMATION_SCHEMA.TABLES

;

-- 컬럼 상세 정보 

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'SQUTY02TT'

ORDER BY 5

;