MS-SQL 테이블 컬럼 조작(추가,수정,삭제)
컬럼 추가
ALTER TABLE ${TALBE_NAME} ADD ${COLUMN_NAME} ${COLUMN_TYPE}
1.NULL 허용
•Example
ALTER TABLE BOARD ADD SECURITY CHAR(1) NULL;
2.NULL 허용하지 않음(기본값 설정)
•Exxample
ALTER TABLE BOARD ADD SECURITY 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 SECURITY 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 SECURITY;