참고: http://rocabilly.tistory.com/48

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=?