CREATE PROCEDURE ChangeProcOwner @OldOwner as NVARCHAR(128), @NewOwner as NVARCHAR(128) AS
DECLARE @Name as NVARCHAR(128) DECLARE @Owner as NVARCHAR(128) DECLARE @OwnerName as NVARCHAR(128)
DECLARE curObject CURSOR FOR select 'Name' = name, 'Owner' = user_name(uid) from sysobjects where user_name(uid)=@OldOwner and xtype='p' order by name
OPEN curObject FETCH NEXT FROM curObject INTO @Name, @Owner WHILE(@@FETCH_STATUS=0) BEGIN if @Owner=@OldOwner begin set @OwnerName = @OldOwner + '.' + rtrim(@Name) exec sp_changeobjectowner @OwnerName, @NewOwner end
,@schema_id int
,@schema_name varchar(50)
,@sql_cmd nvarchar(255)declare table_cursor cursor for
select name, schema_id
from sys.tables
where schema_id <> 1 -- dbo 的 schema_id = 1
order by nameopen table_cursorfetch next from table_cursor into @table_name, @schema_idwhile @@fetch_status = 0
begin
print '' select @schema_name = name
from sys.schemas
where schema_id = @schema_id select @sql_cmd = 'alter schema dbo transfer ' + @schema_name + '.' + @table_name print @sql_cmd
exec sp_executesql @sql_cmd fetch next from table_cursor into @table_name, @schema_id
endclose table_cursor
deallocate table_cursor
go这个范例就是把所有非 dbo 名下的 table 都转给 dbo,每个 database 都有一些固定的 Schema,例如 dbo,而 dbo 的 schema_id 固定为 1,所以只要找出每个 table 所属的 Schema,搭配 Alter Schema 的语法就可以了,当然,配合 cursor 的写法,一次搞定。
对象名 'sys.tables' 无效
exec sp_msforeachtable 'sp_changeobjectowner ''?'', ''dbo'''
CREATE PROCEDURE ChangeProcOwner
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS
DECLARE @Name as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128)
DECLARE curObject CURSOR FOR
select 'Name' = name,
'Owner' = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner and xtype='p'
order by name
OPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN
if @Owner=@OldOwner
begin
set @OwnerName = @OldOwner + '.' + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end
FETCH NEXT FROM curObject INTO @Name, @Owner
END
close curObject
deallocate curObject
GO
exec sp_msforeachtable 'sp_changeobjectowner ''?'', ''dbo'''