[MSSQL]테이블 컬럼 변경 및 추가 스크립트

--스크립트 등록시 BEHIN TRAN,ROLLBACK TRAN은 삭제해주세요.
BEGIN TRAN

--1. 컬럼변경
IF EXISTS(SELECT TOP(1) 1 FROM SYSOBJECTS WHERE NAME = 'T_PAY_AbWkSheet' AND xtype = 'U'  )
BEGIN

    IF NOT EXISTS(SELECT TOP(1) 1 FROM SYSCOLUMNS WHERE OBJECT_ID('T_PAY_AbWkSheet') = id and name = 'FromCD')
    BEGIN
        EXEC SP_RENAME 'T_PAY_AbWkSheet.[AbWkMstCD]', 'FromCD', 'COLUMN'
    END
    
    IF NOT EXISTS(SELECT TOP(1) 1 FROM SYSCOLUMNS WHERE OBJECT_ID('T_PAY_AbWkSheet') = id and name = 'FromSerl')
    BEGIN
        EXEC SP_RENAME 'T_PAY_AbWkSheet.[AbWkSerl]', 'FromSerl', 'COLUMN'
    END
END

IF EXISTS(SELECT TOP(1) 1 FROM SYSOBJECTS WHERE NAME = 'T_PAY_AbWkSheet_Log' AND xtype = 'U'  )
BEGIN
    IF NOT EXISTS(SELECT TOP(1) 1 FROM SYSCOLUMNS WHERE OBJECT_ID('T_PAY_AbWkSheet_Log') = id and name = 'FromCD')
    BEGIN
        EXEC SP_RENAME 'T_PAY_AbWkSheet_Log.[AbWkMstCD]', 'FromCD', 'COLUMN'
    END
    
    IF NOT EXISTS(SELECT TOP(1) 1 FROM SYSCOLUMNS WHERE OBJECT_ID('T_PAY_AbWkSheet_Log') = id and name = 'FromSerl')
    BEGIN
        EXEC SP_RENAME 'T_PAY_AbWkSheet_Log.[AbWkSerl]', 'FromSerl', 'COLUMN'
    
    END
END

--2. 컬럼추가
IF EXISTS(SELECT TOP(1) 1 FROM SYSOBJECTS WHERE NAME = 'T_PAY_AbWkSheet' AND xtype = 'U'  )
BEGIN
    IF NOT EXISTS(SELECT TOP(1) 1 FROM SYSCOLUMNS WHERE OBJECT_ID('T_PAY_AbWkSheet') = id and name = 'FromPgmCD')
    BEGIN
        ALTER TABLE T_PAY_AbWkSheet ADD FromPgmCD INT NOT NULL DEFAULT 0
    END
END

IF EXISTS(SELECT TOP(1) 1 FROM SYSOBJECTS WHERE NAME = 'T_PAY_AbWkSheet_Log' AND xtype = 'U'  )
BEGIN
    IF NOT EXISTS(SELECT TOP(1) 1 FROM SYSCOLUMNS WHERE OBJECT_ID('T_PAY_AbWkSheet_Log') = id and name = 'FromPgmCD')
    BEGIN
        ALTER TABLE T_PAY_AbWkSheet_Log ADD FromPgmCD INT NOT NULL DEFAULT 0
    END
END

--3. 백업테이블 만들기
IF EXISTS(SELECT TOP(1) 1 FROM SYSOBJECTS WHERE NAME = 'T_PAY_AbWkSheet' AND xtype = 'U'  )
BEGIN

    IF NOT EXISTS(SELECT TOP(1) 1 FROM SYSOBJECTS WHERE NAME = 'T_PAY_AbWkSheet_BackUp20221207' AND xtype = 'U')
    BEGIN
        SELECT * INTO T_PAY_AbWkSheet_BackUp20221207 FROM  T_PAY_AbWkSheet
    END
END
    
IF EXISTS(SELECT TOP(1) 1 FROM SYSOBJECTS WHERE NAME = 'T_PAY_AbWkSheet_Log' AND xtype = 'U'  )
BEGIN
    IF NOT EXISTS(SELECT TOP(1) 1 FROM SYSOBJECTS WHERE NAME = 'T_PAY_AbWkSheet_Log_BackUp20221207' AND xtype = 'U')
    BEGIN
        SELECT * INTO T_PAY_AbWkSheet_Log_BackUp20221207 FROM  T_PAY_AbWkSheet_Log
    END
END

--4. 기존테이블 삭제
IF EXISTS(SELECT TOP(1) 1 FROM SYSOBJECTS WHERE NAME = 'T_PAY_AbWkSheet' AND xtype = 'U'  )
BEGIN
    IF EXISTS(SELECT TOP(1) 1 FROM SYSOBJECTS WHERE NAME = 'T_PAY_AbWkSheet_BackUp20221207' AND xtype = 'U'  )
    BEGIN
        DROP TABLE T_PAY_AbWkSheet
    END
END

IF EXISTS(SELECT TOP(1) 1 FROM SYSOBJECTS WHERE NAME = 'T_PAY_AbWkSheet_Log' AND xtype = 'U'  )
BEGIN
    IF EXISTS(SELECT TOP(1) 1 FROM SYSOBJECTS WHERE NAME = 'T_PAY_AbWkSheet_Log_BackUp20221207' AND xtype = 'U'  )
    BEGIN
        DROP TABLE T_PAY_AbWkSheet_Log
    END
END
--5. proDoc[테이블정보등록]화면에서 테이블 등록후=> [스크립트생성]
--6.[스크립트생성]된 테이블 생성
--신청근태반영
IF NOT EXISTS(SELECT TOP(1) 1 FROM SYSOBJECTS WHERE NAME = 'T_PAY_AbWkSheet' AND xtype = 'U'  )
BEGIN
   CREATE TABLE T_PAY_AbWkSheet
   ( AbWkShtCD     INT                   NOT NULL   --내부코드
    ,FromCD        INT                   NOT NULL   --신청마스터코드
    ,FromSerl      INT                   NOT NULL   --신청순번
    ,FromPgmCD     INT                   NOT NULL   --진행코드
    ,WkDate        NCHAR(8)              NOT NULL   --일자
    ,DayCD         INT                   NOT NULL   --요일코드
    ,EmpCD         INT                   NOT NULL   --사원코드
    ,DeptCD        INT                   NOT NULL   --부서코드
    ,WkItemCD      INT                   NOT NULL   --근태코드
    ,SWkTime       NCHAR(4)              NOT NULL   --시작시간
    ,EWkTime       NCHAR(4)              NOT NULL   --종료시간
    ,AbWkMinute    DECIMAL(19,5)         NOT NULL   --근태시간(분)
    ,Reason        NVARCHAR(1000)        NOT NULL   --사유
    ,Remark        NVARCHAR(1000)        NOT NULL   --비고
    ,LastUserCD    INT                   NOT NULL   --최종작업자
    ,LastDateTime  DATETIME              NOT NULL   --최종작업일시
    ,SWkTimeAdd    INT                   NOT NULL   --시작시간구분
    ,EWkTimeAdd    INT                   NOT NULL   --종료시간구분
CONSTRAINT TPK_T_PAY_AbWkSheet PRIMARY KEY CLUSTERED (AbWkShtCD ASC)
)
CREATE UNIQUE  INDEX IDX_T_PAY_AbWkSheet ON T_PAY_AbWkSheet (AbWkShtCD     )
END


--7.생성된 테이블에 백업테이블 INSERT
IF NOT EXISTS(SELECT 1 FROM T_PAY_AbWkSheet   )
BEGIN
    INSERT INTO T_PAY_AbWkSheet(
                                 AbWkShtCD     
                                ,FromCD        
                                ,FromSerl      
                                ,FromPgmCD     
                                ,WkDate        
                                ,DayCD         
                                ,EmpCD         
                                ,DeptCD        
                                ,WkItemCD      
                                ,SWkTime       
                                ,EWkTime       
                                ,AbWkMinute    
                                ,Reason        
                                ,Remark        
                                ,LastUserCD    
                                ,LastDateTime  
                                ,SWkTimeAdd    
                                ,EWkTimeAdd                                                                                                                                                                                                                                                                                                    
                                                                                                
                                )
    SELECT AbWkShtCD
          ,FromCD
          ,FromSerl
          ,FromPgmCD
          ,WkDate
          ,DayCD
          ,EmpCD
          ,DeptCD
          ,WkItemCD
          ,SWkTime
          ,EWkTime
          ,AbWkMinute
          ,Reason
          ,Remark
          ,LastUserCD
          ,LastDateTime
          ,SWkTimeAdd
          ,EWkTimeAdd
     FROM T_PAY_AbWkSheet_BackUp20221207
END
--로그테이블은 따로 INSERT 안한상태입니다.

SELECT * FROM T_PAY_AbWkSheet
SELECT * FROM T_PAY_AbWkSheet_BackUp20221207
ROLLBACK TRAN