다수의 테이블에 컬럼 추가하기 (존재 여부 검사)
시스템 테이블(sys.tables)을 통해 다수의 테이블을 필터링하고, 임시 테이블 변수에 저장.
추가할 컬럼을 설정하고, 임시테이블 변수를 커서로 오픈하여 이터레이션하며 컬럼이 이미 존재하는지 여부 확인 후 추가.
컬럼을 단순 이름보다 타입, 길이, 널 허용 여부 등등을 임시 테이블 변수나 delimiter를 활용한 문자열 등으로 확장하여 개선할 수 있다.
DECLARE @tblNames TABLE (
tbl_nm nvarchar(200)
);
INSERT INTO @tblNames SELECT [name] FROM sys.tables WHERE [name] LIKE '%<테이블 필터링>%';
SELECT * FROM @tblNames;
DECLARE @name varchar(200),
@col_1 varchar(200) = '<추가할 컬럼>',
@col_2 varchar(200) = '<추가할 컬럼>',
@col_3 varchar(200) = '<추가할 컬럼>',
@col_4 varchar(200) = '<추가할 컬럼>',
@col_5 varchar(200) = '<추가할 컬럼>';
DECLARE M_CUR CURSOR
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT tbl_nm FROM @tblNames
OPEN M_CUR
FETCH NEXT FROM M_CUR INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @name
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(@name) AND name = @col_1)
BEGIN
EXEC ('alter table ' + @name + ' add ' + @col_1 + ' varchar(250); print ''' + @name + ' > ' + @col_1 + '''')
END;
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(@name) AND name = @col_2)
BEGIN
EXEC ('alter table ' + @name + ' add ' + @col_2 + ' varchar(250); print ''' + @name + ' > ' + @col_2 + '''')
END;
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(@name) AND name = @col_3)
BEGIN
EXEC ('alter table ' + @name + ' add ' + @col_3 + ' varchar(250); print ''' + @name + ' > ' + @col_3 + '''')
END;
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(@name) AND name = @col_4)
BEGIN
EXEC ('alter table ' + @name + ' add ' + @col_4 + ' varchar(250); print ''' + @name + ' > ' + @col_4 + '''')
END;
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(@name) AND name = @col_5)
BEGIN
EXEC ('alter table ' + @name + ' add ' + @col_5 + ' varchar(250); print ''' + @name + ' > ' + @col_5 + '''')
END;
FETCH NEXT FROM M_CUR INTO @name
END
CLOSE M_CUR
DEALLOCATE M_CUR