Contents

1 스키마 간에 보안 개체 이동하기

set nocount on
set statistics io off

declare 
	@sql nvarchar(4000)
,	@new_schema nvarchar(500)
,	@old_schema nvarchar(500)
,	@type nvarchar(500)

set @old_schema = '옛날_스키마'
set @new_schema = '새로_옮겨갈_스키마'
set @type = 'U'

/* type컬럼값 중에 1개 입력
select distinct type, type_desc from sys.all_objects

type type_desc
---- ------------------------------------------------------------
FN   SQL_SCALAR_FUNCTION
IF   SQL_INLINE_TABLE_VALUED_FUNCTION
F    FOREIGN_KEY_CONSTRAINT
U    USER_TABLE
SQ   SERVICE_QUEUE
D    DEFAULT_CONSTRAINT
S    SYSTEM_TABLE
P    SQL_STORED_PROCEDURE
PK   PRIMARY_KEY_CONSTRAINT
V    VIEW
IT   INTERNAL_TABLE
X    EXTENDED_STORED_PROCEDURE
PC   CLR_STORED_PROCEDURE
TF   SQL_TABLE_VALUED_FUNCTION
*/

declare cur cursor for
	select 
		'alter schema [' + @new_schema + '] transfer [' + @old_schema + '].[' + name + ']'
	from sys.all_objects
	where type = @type
	and [schema_id] = schema_id(@old_schema)

open cur
fetch next from cur into @sql

while @@fetch_status = 0
begin
	print @sql
	--exec(@sql) --스키마를 옮기는 것이므로 print결과를 확인 후 복사->붙여넣기->실행 의 순서를 지키는 것을 권장함.
	fetch next from cur into @sql
end

close cur
deallocate cur