MSSQL 컬럼 추가, 수정, 삭제

--컬럼 추가

ALTER TABLE ${TALBE_NAME} ADD ${COLUMN_NAME} ${COLUMN_TYPE};

---

--NULL 허용

Example
ALTER TABLE BOARD ADD USE_YN CHAR(1) NULL;

---

--NULL 허용하지 않음(기본값 설정)

Example
ALTER TABLE BOARD ADD USE_YN CHAR(1) NOT NULL CONSTRAINT DF_ADDR DEFAULT 'N';

---

--컬럼 수정

ALTER TABLE ${TABLE_NAME} ALTER COLUMN ${COLUMN_NAME} ${COLUMN_TYPE}

Example
ALTER TABLE BOARD ALTER COLUMN USE_YN VARCHAR(1) NULL;

 

---

--컬럼명 수정

EXEC SP_RENAME '${TABLE_NAME.[OLD_COLUMN_NAME]}', '${NEW_COLUMN_NAME}', 'COLUMN'

Example
EXEC SP_RENAME 'BOARD.[SECURITY]', 'NEW_SECURITY', 'COLUMN';

---

--컬럼 삭제

ALTER TABLE ${TABLE_NAME} DROP COLUMN ${COLUMN_NAME};

Example
ALTER TABLE BOARD DROP COLUMN BOARD_SORT;

---