수정할 곳이 있습니다만 이렇게 적용하시고

다시 널을 수용하지 못하게 고치시면 어떨까요?

그리고 좀 더 응용해서 다른테이블에 그 해당 널 인지 아닌지 체크도 해두시면 더욱 좋을 것 같습니다.

 

이렇게 프로시져로 할 수 있을것 같아 그냥 아이디어 제공입니다.

 

 

create table tech
(
idx int not null
,colx char(10) not null
,constraint [check_tech_idx] check  (idx  > 0)
)

drop table tech

insert into tech values (null)
서버: 메시지 515, 수준 16, 상태 2, 줄 1
'pubs.dbo.tech' 테이블, 'idx' 열에 NULL 값을 삽입할 수 없습니다. 열에서 null을 사용할 수 없습니다. INSERT이(가) 실패했습니다.
문이 종료되었습니다.

alter table tech
alter column idx int  null

insert into tech values (null)
(1개 행 적용됨)
select * from tech
idx        
-----------
NULL

(1개 행 적용됨)

update tech set idx = 1 where idx is null

alter table tech
alter column idx int not null

sp_help tech
Nullable no

-- drop table tech

select table_name into ##basetable from information_schema.tables where table_type='Base Table'
select * from ##basetable

create proc USP_TableColToNull
(
@tableName char(100)
)
as

-- 만든이 석이
-- 20050809 1115

declare @colCount int

declare @column_name char(100)
declare @data_type char(10)
declare @CHARACTER_MAXIMUM_LENGTH char(10)
declare @actionSql varchar(1000)

select identity(int,1,1) as idx, column_name, data_type,CHARACTER_MAXIMUM_LENGTH into #thisTableColName from information_schema.columns where table_name='tech' -- 변수로 받을것
select @colCount = count(*) from #thisTableColName
select @colCount
select * from #thisTableColName
while (@colCount >= 1) begin
 select @column_name = column_name, @data_type = data_type, @CHARACTER_MAXIMUM_LENGTH = CHARACTER_MAXIMUM_LENGTH  from #thisTableColName where idx = @colCount
 select @column_name , @data_type, @CHARACTER_MAXIMUM_LENGTH
-- alter table tech alter column idx int not  null
 if ( @data_type = 'int' ) begin -- 데이터 타입 에 해당하는것 모두 적용할 것
 set @actionSql = 'alter table tech alter column ' + rtrim(@column_name) +' '+ rtrim(@data_type) + ' null'
--   print @actionSql
 end else begin
 set @actionSql = 'alter table tech alter column ' + rtrim(@column_name) +' ' +rtrim(@data_type) + '(' + rtrim(@CHARACTER_MAXIMUM_LENGTH) + ') null'
--   print @actionSql
 end
 exec (@actionSql)
 set @colCount = @colCount - 1
end
drop table #thisTableColName

go

 

-- 커서를 통한 모든 테이블 적용
DECLARE CUR_ColToNull CURSOR
READ_ONLY
FOR SELECT table_name FROM ##basetable
DECLARE @tableName varchar(100)
OPEN CUR_ColToNull

FETCH NEXT FROM CUR_ColToNull INTO @tableName
WHILE (@@fetch_status <> -1)
BEGIN
 IF (@@fetch_status <> -2)
 BEGIN
  exec USP_TableColToNull @tableName
 END
 FETCH NEXT FROM CUR_ColToNull INTO @tableName
END

CLOSE CUR_ColToNull
DEALLOCATE CUR_ColToNull
GO

drop table ##basetable
go

 

(2개 행 적용됨)

           
-----------
2

(1개 행 적용됨)

idx         column_name                                                                                                                      data_type                                                                                                                        CHARACTER_MAXIMUM_LENGTH
----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------
1           idx                                                                                                                              int                                                                                                                              NULL
2           colx                                                                                                                             char                                                                                                                             10

(2개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
colx                                                                                                 char       10       

(1개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
idx                                                                                                  int        NULL

(1개 행 적용됨)

(2개 행 적용됨)

           
-----------
2

(1개 행 적용됨)

idx         column_name                                                                                                                      data_type                                                                                                                        CHARACTER_MAXIMUM_LENGTH
----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------
1           idx                                                                                                                              int                                                                                                                              NULL
2           colx                                                                                                                             char                                                                                                                             10

(2개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
colx                                                                                                 char       10       

(1개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
idx                                                                                                  int        NULL

(1개 행 적용됨)

(2개 행 적용됨)

           
-----------
2

(1개 행 적용됨)

idx         column_name                                                                                                                      data_type                                                                                                                        CHARACTER_MAXIMUM_LENGTH
----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------
1           idx                                                                                                                              int                                                                                                                              NULL
2           colx                                                                                                                             char                                                                                                                             10

(2개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
colx                                                                                                 char       10       

(1개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
idx                                                                                                  int        NULL

(1개 행 적용됨)

(2개 행 적용됨)

           
-----------
2

(1개 행 적용됨)

idx         column_name                                                                                                                      data_type                                                                                                                        CHARACTER_MAXIMUM_LENGTH
----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------
1           idx                                                                                                                              int                                                                                                                              NULL
2           colx                                                                                                                             char                                                                                                                             10

(2개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
colx                                                                                                 char       10       

(1개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
idx                                                                                                  int        NULL

(1개 행 적용됨)

(2개 행 적용됨)

           
-----------
2

(1개 행 적용됨)

idx         column_name                                                                                                                      data_type                                                                                                                        CHARACTER_MAXIMUM_LENGTH
----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------
1           idx                                                                                                                              int                                                                                                                              NULL
2           colx                                                                                                                             char                                                                                                                             10

(2개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
colx                                                                                                 char       10       

(1개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
idx                                                                                                  int        NULL

(1개 행 적용됨)

(2개 행 적용됨)

           
-----------
2

(1개 행 적용됨)

idx         column_name                                                                                                                      data_type                                                                                                                        CHARACTER_MAXIMUM_LENGTH
----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------
1           idx                                                                                                                              int                                                                                                                              NULL
2           colx                                                                                                                             char                                                                                                                             10

(2개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
colx                                                                                                 char       10       

(1개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
idx                                                                                                  int        NULL

(1개 행 적용됨)

(2개 행 적용됨)

           
-----------
2

(1개 행 적용됨)

idx         column_name                                                                                                                      data_type                                                                                                                        CHARACTER_MAXIMUM_LENGTH
----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------
1           idx                                                                                                                              int                                                                                                                              NULL
2           colx                                                                                                                             char                                                                                                                             10

(2개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
colx                                                                                                 char       10       

(1개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
idx                                                                                                  int        NULL

(1개 행 적용됨)

(2개 행 적용됨)

           
-----------
2

(1개 행 적용됨)

idx         column_name                                                                                                                      data_type                                                                                                                        CHARACTER_MAXIMUM_LENGTH
----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------
1           idx                                                                                                                              int                                                                                                                              NULL
2           colx                                                                                                                             char                                                                                                                             10

(2개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
colx                                                                                                 char       10       

(1개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
idx                                                                                                  int        NULL

(1개 행 적용됨)

(2개 행 적용됨)

           
-----------
2

(1개 행 적용됨)

idx         column_name                                                                                                                      data_type                                                                                                                        CHARACTER_MAXIMUM_LENGTH
----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------
1           idx                                                                                                                              int                                                                                                                              NULL
2           colx                                                                                                                             char                                                                                                                             10

(2개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
colx                                                                                                 char       10       

(1개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
idx                                                                                                  int        NULL

(1개 행 적용됨)

(2개 행 적용됨)

           
-----------
2

(1개 행 적용됨)

idx         column_name                                                                                                                      data_type                                                                                                                        CHARACTER_MAXIMUM_LENGTH
----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------
1           idx                                                                                                                              int                                                                                                                              NULL
2           colx                                                                                                                             char                                                                                                                             10

(2개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
colx                                                                                                 char       10       

(1개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
idx                                                                                                  int        NULL

(1개 행 적용됨)

(2개 행 적용됨)

           
-----------
2

(1개 행 적용됨)

idx         column_name                                                                                                                      data_type                                                                                                                        CHARACTER_MAXIMUM_LENGTH
----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------
1           idx                                                                                                                              int                                                                                                                              NULL
2           colx                                                                                                                             char                                                                                                                             10

(2개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
colx                                                                                                 char       10       

(1개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
idx                                                                                                  int        NULL

(1개 행 적용됨)

(2개 행 적용됨)

           
-----------
2

(1개 행 적용됨)

idx         column_name                                                                                                                      data_type                                                                                                                        CHARACTER_MAXIMUM_LENGTH
----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------
1           idx                                                                                                                              int                                                                                                                              NULL
2           colx                                                                                                                             char                                                                                                                             10

(2개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
colx                                                                                                 char       10       

(1개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
idx                                                                                                  int        NULL

(1개 행 적용됨)

(2개 행 적용됨)

           
-----------
2

(1개 행 적용됨)

idx         column_name                                                                                                                      data_type                                                                                                                        CHARACTER_MAXIMUM_LENGTH
----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------
1           idx                                                                                                                              int                                                                                                                              NULL
2           colx                                                                                                                             char                                                                                                                             10

(2개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
colx                                                                                                 char       10       

(1개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
idx                                                                                                  int        NULL

(1개 행 적용됨)

(2개 행 적용됨)

           
-----------
2

(1개 행 적용됨)

idx         column_name                                                                                                                      data_type                                                                                                                        CHARACTER_MAXIMUM_LENGTH
----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------
1           idx                                                                                                                              int                                                                                                                              NULL
2           colx                                                                                                                             char                                                                                                                             10

(2개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
colx                                                                                                 char       10       

(1개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
idx                                                                                                  int        NULL

(1개 행 적용됨)

(2개 행 적용됨)

           
-----------
2

(1개 행 적용됨)

idx         column_name                                                                                                                      data_type                                                                                                                        CHARACTER_MAXIMUM_LENGTH
----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------
1           idx                                                                                                                              int                                                                                                                              NULL
2           colx                                                                                                                             char                                                                                                                             10

(2개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
colx                                                                                                 char       10       

(1개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
idx                                                                                                  int        NULL

(1개 행 적용됨)

(2개 행 적용됨)

           
-----------
2

(1개 행 적용됨)

idx         column_name                                                                                                                      data_type                                                                                                                        CHARACTER_MAXIMUM_LENGTH
----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------
1           idx                                                                                                                              int                                                                                                                              NULL
2           colx                                                                                                                             char                                                                                                                             10

(2개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
colx                                                                                                 char       10       

(1개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
idx                                                                                                  int        NULL

(1개 행 적용됨)

(2개 행 적용됨)

           
-----------
2

(1개 행 적용됨)

idx         column_name                                                                                                                      data_type                                                                                                                        CHARACTER_MAXIMUM_LENGTH
----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------
1           idx                                                                                                                              int                                                                                                                              NULL
2           colx                                                                                                                             char                                                                                                                             10

(2개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
colx                                                                                                 char       10       

(1개 행 적용됨)

                                                                                                                          
---------------------------------------------------------------------------------------------------- ---------- ----------
idx                                                                                                  int        NULL

(1개 행 적용됨)

이런식으로 테이블 수만큼 돌아 갑니다. 변수로 들어가는 테이블 이름을 여기서 다 바꾸면 안되니 전 그냥 tech 로 고정 시켰습니다. 스크립트 실행후 모든 테이블의 컬럼은 Nullable = yes 입니다.

 

다시 복구하는건 알아서 응용하시길 바랍니다. 안되면 이렇게라도 하면 될것 같아서요 ^^

 

원래 컬럼 정의는 null 허용이었으나 다른 관리자가 널이면 에러가 나겠군 해서 테이블 수준 제약을 나중에 걸었겠죠...

그럼 화이팅 입니다.

 

위 sp는 석이님많이 해낼수 있는 프로시저였습니다.^^;

정말 감사드립니다.

아.그런데요..원래대로 복구할려면, 어느컬럼들이 not null 이었는지도

알아야할것 같은데,,이미 다 변경된 후라 ,복구작업이 불가능할것 같은데요...아니면, Er-win에서 떠온 erd로 변경작업할수 있을까요??

이젠 복구잡업이 눈에 거슬리네요.^^;