생성된 테이블 전체조회

-- MS-SQL
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%{검색어}%'
ORDER BY  TABLE_NAME

생성된 뷰 전체조회

-- MS-SQL
SELECT * FROM INFORMATION_SCHEMA.VIEWS
;

PK, FK정보 확인

SELECT
    acc.OWNER AS TABLE_OWNER,
    acc.TABLE_NAME,
    ac.CONSTRAINT_NAME,
    acc.COLUMN_NAME,
    acc.POSITION
FROM
    ALL_CONS_COLUMNS acc
    JOIN ALL_CONSTRAINTS ac ON acc.OWNER = ac.OWNER AND acc.CONSTRAINT_NAME = ac.CONSTRAINT_NAME
WHERE
    ac.CONSTRAINT_TYPE = 'P'
    AND acc.OWNER = 'MIS'
    AND acc.TABLE_NAME = 'RFMSEXPD'
ORDER BY
    acc.POSITION

컬럼정보 확인

-- MS-SQL
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'SAUTODOCUD'
ORDER BY 1
;
​
-- MS-SQL
SELECT
   a.name AS 테이블명
FROM
   sys.tables a
INNER JOIN
   sys.columns b
ON
   a.object_id = b.object_id
WHERE
   b.name = '원하는컬럼명'
;
​
-- ORACLE
SELECT
    TABLE_NAME
    ,COLUMN_NAME
    ,DATA_TYPE
FROM ALL_TAB_COLUMNS
WHERE 1=1
AND COLUMN_NAME = '컬럼명'
​
-- ORACLE
SELECT * FROM COLS WHERE TABLE_NAME = '테이블이름';
​
-- MS-SQL
select * from sctrl
where
    CTRL_CD = 'B1'
;
​
​

PK조회

-- MYSQL
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME= '{테이블명}'
ORDER BY ORDINAL_POSITION
​
-- ORACLE
SELECT A.TABLE_NAME
     , A.CONSTRAINT_NAME
     , B.COLUMN_NAME
     , B.POSITION
  FROM ALL_CONSTRAINTS  A
     , ALL_CONS_COLUMNS B
 WHERE A.TABLE_NAME      = '{테이블명}'
   AND A.CONSTRAINT_TYPE = 'P'
   AND A.OWNER           = B.OWNER
   AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
 ORDER BY B.POSITION

권한조회

-- oracle 테이블에 접근할 수 있는 권한
SELECT * FROM USER_TAB_PRIVS
WHERE TABLE_NAME IN ('RFMSEXPD')
​
SELECT * FROM USER_TAB_PRIVS
WHERE TABLE_NAME LIKE '%CARD_RCPT%'