March 23, 2012

Schema conversion for SAP database copy [custom direct]

-----------begin change of schema----
use EH5
go
if object_id('sp_change_sapuser') is not null drop procedure sp_change_sapuser go create procedure 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)
select @oldid_uid =schema_id from EH5.sys.schemas where name=@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

exec sp_change_sapuser 'mp1', 'eh5'
go
---------------end change of schema-------------------------