시놀로지 MSSQL 설치 따라하기 #3 (22년 6월) - SSMS에서 테이블 관리

DB백업을 하다보면 다시 백업을 해야할때가 있습니다.  이럴때 이전 테이블을 다 지우고 다시 백업하는 편이 나을때가 있는데 아래와 같은 과정으로 진행하면 됩니다. 

 

아래의 명령문은  당근로리야스 님의 블로그에서 발췌해서 기록해두었습니다. 2013년도 글입니다. 

https://blog.danggun.net/1854

 

sys.object 사용해서 테이블 리스트 확인하기

Select name
From sys.objects
Where type = 'U'

테이블 검색해서 지우기

Select 'Drop Table ' + name As Command
From sys.objects
Where type='U'

프로시져 검색해서 지우기

--저장 프로시저
Select 'Drop Procedure ' + name As Command
From sys.objects
Where type='P'

뷰 검색해서 지우기

--뷰
Select 'Drop View ' + name As Command
From sys.objects
Where type='V'

커서 사용해서 한번에 지우기

--명령을 임시로 받을 테이블
Declare @tableCommand Table
( Command nvarchar(128));

--명령생성
insert into @tableCommand Select 'Drop Table ' + name As Command
							From sys.objects
							Where type='U'

--select *
--from @tableCommand


--커서 사용시 명령을 임시로 넣을 변수
Declare @sCommand nvarchar(128);

Declare cursorDrop cursor
    For Select Command
        From @tableCommand;

Open cursorDrop;

Fetch Next From cursorDrop Into @sCommand;

While(@@FETCH_STATUS <> -1)
Begin;
    --명령 실행
    Exec sp_executesql @sCommand;
 
    --다음 결과 데이터로 이동
    Fetch Next From cursorDrop Into @sCommand;
End;

Close cursorDrop;
Deallocate cursorDrop;

 

이 아래는 응용을 좀 해봤는데요, 테이블을 복사해오면 PK설정이 안돼있어서, 이걸 일일이 해주려니 보통일이 아니더군요. PK설정도 아래의 단계로 문장을 생성하고 반복실행을 해보니 잘 돌아갑니다. 

 

프리머리 키 설정

ALTER TABLE 테이블명 
   ADD CONSTRAINT 테이블명_PK PRIMARY KEY (ID);

Select 'ALTER TABLE ' + name + ' ADD CONSTRAINT ' + name + '_PK PRIMARY KEY (ID);'As Command
From sys.objects
Where type='U'

아래와 같이 하면 테이블에 ID를 가진 모든 테이블은 PK로 지정이 되고, ID 필드가 없는 테이블은 에러로 지나친다.

Declare @tableCommand Table
( Command nvarchar(128));

--명령생성
insert into @tableCommand Select 'ALTER TABLE ' + name + ' ADD CONSTRAINT ' + name + '_PK PRIMARY KEY (ID);'As Command
							From sys.objects
							Where type='U'

--select *
--from @tableCommand


--커서 사용시 명령을 임시로 넣을 변수
Declare @sCommand nvarchar(128);

Declare cursorDrop cursor
    For Select Command
        From @tableCommand;

Open cursorDrop;

Fetch Next From cursorDrop Into @sCommand;

While(@@FETCH_STATUS <> -1)
Begin;
    --명령 실행
    Exec sp_executesql @sCommand;
 
    --다음 결과 데이터로 이동
    Fetch Next From cursorDrop Into @sCommand;
End;

Close cursorDrop;
Deallocate cursorDrop;

이 다음 문제가 하나 더 발생, ID의 자동증분설정.

문제가 생겼습니다. 아래는 문제를 푸는 과정입니다. 끝까지 읽고 사용할 부분으로 판단되는 부분만 사용해주세요.

first run

Alter TABLE [dbo].[t_name]
    drop column [id]

then run this

Alter TABLE [dbo].[t_name]

add  [id] [int] IDENTITY(1,1) NOT NULL

 

or

ALTER TABLE (yourTable) ADD NewColumn INT IDENTITY(1,1)

ALTER TABLE (yourTable) DROP COLUMN OldColumnName

이걸또 한방에 한다면? 

 

https://www.c-sharpcorner.com/article/working-with-identity-column-after-table-creation-in-sql-server/

Working With Identity Column After Table Creation In SQL Server

https://sqlserverguides.com/identity-column-in-sql-server/

Identity Column in SQL Server - SQL Server Guides

 

* Updated 22년 6월 15일
레퍼런스를 찾아보면서 이런 저런 시도를 했지만, 우선 내린 결론은 table export만으로는 해결이 불가능 하다고 판단했습니다. 결론은 테이블가져온뒤, 새로운 ID컬럼을 만들고 PK설정후 기존의 ID컬럼을 지운후, 새로운 ID컬럼을 'ID'라는 이름으로 rename해야하는 과정을 거쳐야하는데, 더 찾아보면서 가진걸 최대한 동원해봐야겠습니다.

 

1) 새로운 ID넣기 (PK기능까지)

아래 코드를 실행시키니

alter table 테이블명 add 새로운ID이름 int primary key clustered identity(1,1) not null;

--alter table 테이블명 add IDnew int primary key clustered identity(1,1) not null;

그림과 같이 생성이 됐고, 

테이블을 열어보니

ID가 적용됐습니다.

 

2) 기존 ID필드 삭제하기

아래의 코드를 실행시키니, 

alter table 테이블명 drop column [id];

그림과 같이 가장 위에 있던 ID 필드가 삭제됐습니다.

3) 리네임하기

sp_rename 'AAA.[idnew]', 'id', 'COLUMN'

--AAA 테이블의 idnew이라는 컬럼을 id 라는 이름으로 변경

변경이 됐습니다.

이제 위에서 사용했던 3단계 코드와 한참 위에서 반복문으로 사용했던 두가지 재료를 가져와야겠습니다. 

alter table 테이블명 add newID int primary key clustered identity(1,1) not null;

alter table 테이블명 drop column [id];

sp_rename 'AAA.[idnew]', 'id', 'COLUMN'

Select 'Drop Table ' + name As Command
From sys.objects
Where type='U'

--명령을 임시로 받을 테이블
Declare @tableCommand Table
( Command nvarchar(128));

--명령생성
insert into @tableCommand Select 'Drop Table ' + name As Command
							From sys.objects
							Where type='U'

--select *
--from @tableCommand


--커서 사용시 명령을 임시로 넣을 변수
Declare @sCommand nvarchar(128);

Declare cursorDrop cursor
    For Select Command
        From @tableCommand;

Open cursorDrop;

Fetch Next From cursorDrop Into @sCommand;

While(@@FETCH_STATUS <> -1)
Begin;
    --명령 실행
    Exec sp_executesql @sCommand;
 
    --다음 결과 데이터로 이동
    Fetch Next From cursorDrop Into @sCommand;
End;

Close cursorDrop;
Deallocate cursorDrop;

위의 두가지를  이용해봐야겠네요.

Select 'alter table ' + name + ' add newID int primary key clustered identity(1,1) not null;'  As Command
From sys.objects
Where type='U'

Select 'alter table  ' + name + ' drop column [id];' As Command
From sys.objects
Where type='U'

Select 'sp_rename ''' + name + '.[idnew]'''+ ',' + '''ID'''+','+'''COLUMN'''  As Command
From sys.objects
Where type='U'

점점 더 복잡해지는..내 코드

ALTER TABLE Table_name DROP COLUMN IF EXISTS Column_name

일단은 여기까지는 풀렸는데...이제 인서트를 해봐야겠습니다. (잠시후)

MS ACCESS와 연결해서 데이터 입력을 해봤는데 문제없이 잘 진행됐습니다.

 

아래의 insert into 부분은 한줄씩 실행해서 (--로 잡아놓고) 진행했습니다.

한 번에 해도 될것 같긴한데, 우선 그렇게 진행되서 만약 한번에 해서 경고가 아닌 에러가 나오면 한줄씩 실행해보세요.

 

 

--// ID드랍후, newID를 PK로 생성후, ID로 이름변환
--명령을 임시로 받을 테이블
Declare @tableCommand Table
( Command nvarchar(128));

--명령생성

insert into @tableCommand Select 'ALTER TABLE ' + name + ' DROP COLUMN IF EXISTS [ID];'As Command From sys.objects Where type='U'
insert into @tableCommand Select 'alter table ' + name + ' add newID int primary key clustered identity(1,1) not null;'  As Command  From sys.objects Where type='U'
insert into @tableCommand Select 'sp_rename ''' + name + '.[newID]'''+ ',' + '''ID'''+','+'''COLUMN'''  As Command  From sys.objects Where type='U'

--select *
--from @tableCommand


--커서 사용시 명령을 임시로 넣을 변수
Declare @sCommand nvarchar(128);

Declare cursorDrop cursor
    For Select Command
        From @tableCommand;

Open cursorDrop;

Fetch Next From cursorDrop Into @sCommand;

While(@@FETCH_STATUS <> -1)
Begin;
    --명령 실행
    Exec sp_executesql @sCommand;
 
    --다음 결과 데이터로 이동
    Fetch Next From cursorDrop Into @sCommand;
End;

Close cursorDrop;
Deallocate cursorDrop;

--//테이블 지우기 사용하기 쉽게 내렸음

--명령을 임시로 받을 테이블
Declare @tableCommand Table
( Command nvarchar(128));

--명령생성
insert into @tableCommand Select 'Drop Table ' + name As Command
							From sys.objects
							Where type='U'

--select *
--from @tableCommand


--커서 사용시 명령을 임시로 넣을 변수
Declare @sCommand nvarchar(128);

Declare cursorDrop cursor
    For Select Command
        From @tableCommand;

Open cursorDrop;

Fetch Next From cursorDrop Into @sCommand;

While(@@FETCH_STATUS <> -1)
Begin;
    --명령 실행
    Exec sp_executesql @sCommand;
 
    --다음 결과 데이터로 이동
    Fetch Next From cursorDrop Into @sCommand;
End;

Close cursorDrop;
Deallocate cursorDrop;