데이터베이스 오브젝트의 스키마 일괄 변경

sp_change_sapuser 'oldid', 'newid'

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[sp_change_sapuser]    Script Date: 2021-04-29 오전 8:13:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



create procedure [dbo].[sp_change_sapuser] @oldid sysname, @newid sysname
as begin
									
declare @oldid_uid smallint
declare @newid_uid smallint 
declare @object sysname
declare @object_full nvarchar(999)
									
set @oldid_uid = user_id(@oldid)
set @newid_uid = user_id(@newid)
									
if @oldid_uid is not null and @newid_uid is not null
	begin
	declare object_cursor cursor local for 
		select name
 from sysobjects
 where
 ( (xtype='U' and name <> 'dtproperties')
 or (xtype='V' and name not in ('syssegments','sysconstraints'))
 or (xtype='P' and name not like 'dt_%')
 or (xtype='D' and name not like 'DF__dtpropert%')
 or (xtype in ('FN','TF','IF'))
 ) and @oldid_uid = uid
									
	open object_cursor
	fetch next from object_cursor into @object
	while @@fetch_status=0
	begin
		set @object_full = user_name(@oldid_uid) + '.' + @object
		exec sp_changeobjectowner @object_full, @newid
		fetch next from object_cursor into @object
	end
	end
else
	if @oldid_uid is null
		begin
		print '*** old database user does not exist ***'
		end
	if @newid_uid is null
		begin
		print '*** new database user does not exist ***'
		end
end
						

GO