2022년 5월 10일 화요일
테이블 명세 조회 질의문
=========== MYSQL ========================================================================
SELECT * FROM information_schema.TABLES ;
SELECT * FROM information_schema.COLUMNS ;
SELECT
T.TABLE_NAME ,C.COLUMN_NAME ,C.ORDINAL_POSITION ,C.COLUMN_KEY
, ifnull( IF(C.COLUMN_DEFAULT='NULL',NULL,C.COLUMN_DEFAULT),'') AS COLUMN_DEFAULT
,C.IS_NULLABLE ,C.COLUMN_TYPE
,T.TABLE_COMMENT ,C.COLUMN_COMMENT
,T.TABLE_COLLATION ,C.COLLATION_NAME
FROM information_schema.TABLES T
INNER JOIN information_schema.COLUMNS C
ON T.TABLE_CATALOG = C.TABLE_CATALOG
AND T.TABLE_SCHEMA = C.TABLE_SCHEMA
AND T.TABLE_NAME = C.TABLE_NAME
WHERE 0=0
AND T.TABLE_CATALOG =''
AND T.TABLE_SCHEMA = ''
ORDER BY T.TABLE_NAME , C.ORDINAL_POSITION
;
=========== ORACLE ========================================================================
WITH CONS AS(
SELECT
UC.CONSTRAINT_NAME
,UC.CON_TYPE
,UC.CONSTRAINT_TYPE
,UC.TABLE_NAME
,UC.GENERATED
,UC.STATUS
,UC.INDEX_NAME
,UCC.COLUMN_NAME
,UCC.POSITION
FROM USER_CONSTRAINTS UC
INNER JOIN USER_CONS_COLUMNS UCC
ON UC.OWNER = UCC.OWNER
AND UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
AND UC.TABLE_NAME = UCC.TABLE_NAME
WHERE 0=0
AND UC.OWNER = ''
AND UC.CONSTRAINT_TYPE ='P'
--ORDER BY UC.TABLE_NAME ASC,UCC.POSITION ASC
)
SELECT
TC.TABLE_NAME
,C.COLUMN_NAME
,TC.COMMENTS AS T_COMMENTS
,CC.COMMENTS AS C_COMMENTS
,C.COLUMN_ID
,NVL2(CONS.CONSTRAINT_TYPE ,'PK',NULL) AS CONS
,C.DATA_TYPE
,C.DATA_LENGTH
,C.NULLABLE
,C.DATA_DEFAULT
FROM ALL_TAB_COMMENTS TC
INNER JOIN ALL_TAB_COLUMNS C
ON TC.OWNER = C.OWNER
AND TC.TABLE_NAME = C.TABLE_NAME
INNER JOIN ALL_COL_COMMENTS CC
ON TC.OWNER = CC.OWNER
AND TC.TABLE_NAME = CC.TABLE_NAME
AND C.COLUMN_NAME = CC.COLUMN_NAME
LEFT OUTER JOIN CONS
ON TC.TABLE_NAME = CONS.TABLE_NAME
AND C.COLUMN_NAME = CONS.COLUMN_NAME
WHERE 0=0
AND TC.OWNER = ''
AND TC.TABLE_TYPE = 'TABLE'
ORDER BY TC.TABLE_NAME ASC, C.COLUMN_ID ASC
;
=========== CUBRID ========================================================================
SELECT * FROM DB_CLASS WHERE 0=0
AND OWNER_NAME=''
;
SELECT * FROM DB_ATTRIBUTE WHERE 0=0
;
SELECT * FROM DB_INDEX;
SELECT
T.CLASS_NAME
,C.ATTR_NAME
,T.COMMENT AS T_COMMENT
,C.COMMENT AS C_COMMENT
,C.DEF_ORDER
,C.DATA_TYPE
,C.PREC
,C.SCALE
,C.IS_NULLABLE
,C.COLLATION
FROM DB_CLASS AS T
INNER JOIN DB_ATTRIBUTE AS C
ON T.OWNER_NAME=''
AND T.CLASS_TYPE = 'CLASS'
AND T.CLASS_NAME = C.CLASS_NAME
WHERE 0=0
ORDER BY T.CLASS_NAME,C.DEF_ORDER
;
2022년 1월 17일 월요일
MS-SQL date-format
| 번호 | 쿼리 | 결과 | 코드 | 형식 |
| 0 | CONVERT(CHAR(19),GETDATE(),0) | Jun 7 2018 4:27PM | mon dd yyyy hh:miAM | 기본값 |
| 1 | CONVERT(CHAR(8),GETDATE(),1) | 2006-07-18 | mm/dd/yy | 미국 |
| 2 | CONVERT(CHAR(8),GETDATE(),2) | 18.06.07 | yy/mm/dd | ANSI |
| 3 | CONVERT(CHAR(8),GETDATE(),3) | 2007-06-18 | dd/mm/yy | 영국/프랑스 |
| 4 | CONVERT(CHAR(8),GETDATE(),4) | 07.06.18 | dd.mm.yy | 독일 |
| 5 | CONVERT(CHAR(8),GETDATE(),5) | 2007-06-18 | dd-mm-yy | 이탈리아 |
| 6 | CONVERT(CHAR(9),GETDATE(),6) | 07-Jun-18 | dd mom yy | - |
| 7 | CONVERT(CHAR(10),GETDATE(),7) | 07-Jun-18 | mon dd, yy | - |
| 8 | CONVERT(CHAR(8),GETDATE(),8) | 09:10:50 | hh:mi:ss | - |
| 9 | CONVERT(CHAR(26),GETDATE(),9) | Jun 7 2018 9:10:50:937AM | mon dd yyyy hh:mi:ss:mmmAM | 기본값 |
| 10 | CONVERT(CHAR(8),GETDATE(),10) | 2006-07-18 | mm-dd-yy | 미국 |
| 11 | CONVERT(CHAR(8),GETDATE(),11) | 2018-06-07 | yy/mm/dd | 일본 |
| 12 | CONVERT(CHAR(6),GETDATE(),12) | 180607 | yymmdd | ISO |
| 13 | CONVERT(CHAR(24),GETDATE(),13) | 07 Jun 2018 09:10:50:937 | dd mon yyyy hh:mi:ss:mmm | 유럽기본값 |
| 14 | CONVERT(CHAR(12),GETDATE(),14) | 09:10:50:937 | hh:mi:ss:mmm | - |
| 20 | CONVERT(CHAR(19),GETDATE(),20) | 2018-06-07 10:16 | yyyy-mm-dd hh:mi:ss | ODBC 표준 |
| 21 | CONVERT(CHAR(23),GETDATE(),21) | 16:27.4 | yyyy-mm-dd hh:mi:ss.mmm | ODBC 표준 |
| 22 | CONVERT(CHAR(20),GETDATE(),22) | 2018-06-07 10:16 | yyyy-mm-dd hh:mi:ss AM | - |
| 23 | CONVERT(CHAR(10),GETDATE(),23) | 2018-06-07 | yyyy-mm-dd | - |
| 24 | CONVERT(CHAR(8),GETDATE(),24) | 10:16:27 | hh:mi:ss | - |
| 25 | CONVERT(CHAR(23),GETDATE(),25) | 16:27.4 | yyyy-mm-dd hh:mi:ss.mmm | - |
| 100 | CONVERT(CHAR(19),GETDATE(),100) | Jun 7 2018 4:27PM | mon dd yyyy hh:miAM | 기본값 |
| 101 | CONVERT(CHAR(10),GETDATE(),101) | 06/07/2018 | mm/dd/yyyy | 미국 |
| 102 | CONVERT(CHAR(10),GETDATE(),102) | 2018.06.07 | yyyy/mm/dd | ANSI |
| 103 | CONVERT(CHAR(10),GETDATE(),103) | 07/06/2018 | dd/mm/yyyy | 영국/프랑스 |
| 104 | CONVERT(CHAR(10),GETDATE(),104) | 07.06.2018 | dd.mm.yyyy | 독일 |
| 105 | CONVERT(CHAR(10),GETDATE(),105) | 07-06-2018 | dd-mm-yyyy | 이탈리아 |
| 106 | CONVERT(CHAR(11),GETDATE(),106) | 07-Jun-18 | dd mom yyyy | - |
| 107 | CONVERT(CHAR(12),GETDATE(),107) | 07-Jun-18 | mon dd, yyyy | - |
| 108 | CONVERT(CHAR(8),GETDATE(),108) | 09:10:50 | hh:mi:ss | - |
| 109 | CONVERT(CHAR(26),GETDATE(),109) | Jun 7 2018 9:10:50:937AM | mon dd yyyy hh:mi:ss:mmmAM | 기본값 |
| 110 | CONVERT(CHAR(10),GETDATE(),110) | 06-07-2018 | mm-dd-yyyy | 미국 |
| 111 | CONVERT(CHAR(10),GETDATE(),111) | 2018-06-07 | yyyy/mm/dd | 일본 |
| 112 | CONVERT(CHAR(8),GETDATE(),112) | 20180607 | yyyymmdd | ISO |
| 113 | CONVERT(CHAR(24),GETDATE(),113) | 07 Jun 2018 09:10:50:937 | dd mon yyyy hh:mi:ss:mmm | 유럽기본값 |
| 114 | CONVERT(CHAR(12),GETDATE(),114) | 09:10:50:937 | hh:mi:ss:mmm | - |
| 120 | CONVERT(CHAR(19),GETDATE(),120) | 2018-06-07 10:16 | yyyy-mm-dd hh:mi:ss | ODBC표준 |
| 121 | CONVERT(CHAR(23),GETDATE(),121) | 16:27.4 | yyyy-mm-dd hh:mi:ss.mmm | ODBC표준 |
| 126 | CONVERT(CHAR(23),GETDATE(),126) | 2018-06-07T10:16:27.387 | yyyy-mm-ddThh:mi:ss.mmm | ISO8601 |
| 127 | CONVERT(CHAR(23),GETDATE(),127) | 2018-06-07T10:16:27.387 | yyyy-mm-ddThh:mi:ss.mmmZ | ISO8601 |
| 130 | CONVERT(CHAR(28),GETDATE(),126) | 24 ??? 1439 3:10:25:287PM | dd mon yyyy hh:mi:ss:mmmAM | 회교식 |
| 130 | CONVERT(CHAR(26),GETDATE(),126) | 24/09/1439 3:10:25:287PM | dd/mm/yy hh:mi:ss:mmmAM | 회교식 |
피드 구독하기:
덧글 (Atom)