use [DB명];

SELECT a.TABLE_NAME, a.COLUMN_NAME, '' as description, 
		case when a.CHARACTER_MAXIMUM_LENGTH IS null then a.DATA_TYPE else a.DATA_TYPE + '(' + ltrim(str(a.CHARACTER_MAXIMUM_LENGTH)) + ')' end as DATATYPE, 
        case when a.column_name=isnull(b.column_name,'') and c.constraint_type='PRIMARY KEY' then 'Y' else ' ' end as PK,
        case when a.column_name=isnull(b.column_name,'') and c.constraint_type='FOREIGN KEY' then 'Y' else ' ' end as FK,
        case when a.IS_NULLABLE='YES' then 'Y' else '' end as NOT_NULL,
        case when a.COLUMN_DEFAULT=isnull(a.COLUMN_DEFAULT,'') then a.COLUMN_DEFAULT else ' ' end as DefaultValue
FROM INFORMATION_SCHEMA.COLUMNS  a
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE  b on (a.table_name+a.column_name=b.table_name+b.column_name)
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c on (b.constraint_name=c.constraint_name)
ORDER BY a.TABLE_NAME, a.ORDINAL_POSITION;

설정하는 방법

SET ANSI_WARNINGS OFF;
SET ARITHIGNORE ON;
SET ARITHABORT OFF;

아래는 msdn에 올라온 각각의 옵션에 대한 설명

ANSI_WARNINGS : http://msdn.microsoft.com/ko-kr/library/ms190368.aspx

ARITHIGNORE : http://msdn.microsoft.com/ko-kr/library/ms184341.aspx

ARITHABORT : http://msdn.microsoft.com/ko-kr/library/ms190306.aspx

1. REPLICATE

SELECT REPLICATE('0',6-LEN(EmployeeId)) + EmployeeId

2. RIGHT

SELECT Right('000000' + CONVERT(NVARCHAR, EmployeeID), 6)

개인적으론 2안이 복잡하지 않고 부하가 적어서 더 나은 것 같다.

UPDATE table1
SET col1 = B.col1
FROM table1 A, table2 B
WHERE A.col2=B.col2 AND A.col3=B.col3

UPDATE table_name 
SET col1=CASE ? 
    WHEN '' THEN col1 
    ELSE ? 
END 
WHERE col2=? and col3=?

IF NOT EXISTS (SELECT col1 FROM table_name WHERE col2=? and col3=?) 
    INSERT INTO table_name (col1, col2, col3) VALUES (?, ?, ?) 
ELSE
    UPDATE table_name SET col1=? WHERE col2=? and col3=?

ALTER USER 아이디 IDENTIFIED BY 비밀번호

홑따옴표 때문에 쿼리문 실행 오류가 나서 찾아보니 간단했다.

SELECT TBL.TABLE_NAME , TCM.COMMENTS , TBL.TABLESPACE_NAME , TCL.COLUMN_ID , TCL.COLUMN_NAME , 
	CASE WHEN TCL.DATA_TYPE = 'VARCHAR2' OR TCL.DATA_TYPE = 'CHAR' OR TCL.DATA_TYPE = 'NUMBER' 
		THEN TCL.DATA_TYPE || '(' || DATA_LENGTH || ')'
	ELSE TCL.DATA_TYPE END AS DATA_TYPE , CON.KEY , 
		DECODE(NULLABLE , 'N' , 'NOT NULL' , '') AS NOTNULL , DATA_DEFAULT , CCM.COMMENTS	 
FROM USER_TABLES TBL , USER_TAB_COMMENTS TCM , USER_TAB_COLUMNS TCL , USER_COL_COMMENTS CCM , 
	(SELECT CCL.TABLE_NAME , COLUMN_NAME ,
		CASE WHEN SUM(DECODE(CONSTRAINT_TYPE , 'P' , 1 , 0)) > 0 AND  SUM(DECODE(CONSTRAINT_TYPE , 'F' , 1 , 0)) > 0 
		THEN 'PK,FK'
			WHEN SUM(DECODE(CONSTRAINT_TYPE , 'P' , 1 , 0)) > 0
		THEN 'PK'
			WHEN SUM(DECODE(CONSTRAINT_TYPE , 'F' , 1 , 0)) > 0
		THEN 'FK'
	 	ELSE '' END AS KEY ,
		SUM(DECODE(CONSTRAINT_TYPE , 'C' , 0 , 'P' , 0 , 'F' , 0 , 1)) AS CCC			  
	FROM USER_CONS_COLUMNS CCL , USER_CONSTRAINTS CNS
	WHERE CCL.CONSTRAINT_NAME = CNS.CONSTRAINT_NAME
	GROUP BY CCL.TABLE_NAME , COLUMN_NAME ) CON
WHERE TBL.TABLE_NAME = TCM.TABLE_NAME
	AND TBL.TABLE_NAME = TCL.TABLE_NAME 
	AND TCL.TABLE_NAME = CCM.TABLE_NAME AND TCL.COLUMN_NAME = CCM.COLUMN_NAME
	AND TCL.TABLE_NAME = CON.TABLE_NAME(+) AND TCL.COLUMN_NAME = CON.COLUMN_NAME(+)
ORDER BY TBL.TABLE_NAME , COLUMN_ID

출처 : http://pompsky.blog.me/60051648162

UPDATE table1 a
SET a.col1 = (SELECT col1 FROM table2 WHERE col2 = a.col2)
WHERE a.col2 IN (SELECT col2 FROM table2)

UPDATE table1 a
SET ( col1, col2 ) = ( SELECT col1, col2 FROM table2 b WHERE b.col3=a.col3 ) 
WHERE a.col3 IN ( SELECT col3 FROM table2 )