9、存储更改全部表 CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch2 e: ?+ m4 w0 U) |! x. u
@OldOwner as NVARCHAR(128),. S! w# A4 S7 X* [* a4 n2 F, j9 s# r8 F
@NewOwner as NVARCHAR(128)
0 @$ J; J! `" y) g# ~ AS. }% F- u9 V9 _% }$ ~$ P2 f( `( ?& \( L
DECLARE @Name as NVARCHAR(128)
X$ `4 [- D2 C. \! P- M DECLARE @Owner as NVARCHAR(128)- N- S( |" J, |3 j
DECLARE @OwnerName as NVARCHAR(128)
5 F0 y4 J. t0 z% Y* n DECLARE curObject CURSOR FOR
4 e Q, l) C- f8 d/ J select 'Name' = name,
, N( c1 m) X1 M6 j: C' V7 R 'Owner' = user_name(uid)9 K& l& p$ O. G" {7 G
from sysobjects
# v# |) r; G% S$ @" g% @6 Y( ~ where user_name(uid)=@OldOwner
& i. x3 ~ `8 ^& m order by name
* M$ F4 M/ m5 U OPEN curObject
+ `& \7 [1 \1 t9 ^1 q0 F _! { FETCH NEXT FROM curObject INTO @Name, @Owner
; \4 e1 g7 o+ V' e9 T* b WHILE(@@FETCH_STATUS=0)/ q9 p/ l9 O7 U( {" O
BEGIN. V! C) x8 N7 C
if @Owner=@OldOwner
$ a' d" O* [! [# j begin, p; Q! P. W' v
set @OwnerName = @OldOwner + '.' + rtrim(@Name)" o# @$ ~$ F1 ]
exec sp_changeobjectowner @OwnerName, @NewOwner
. v r. q; J- T# r! X end3 |: E, e4 ~1 z0 q- s( _. Q
-- select @name,@NewOwner,@OldOwner
1 C4 K2 t5 _' m" N9 D FETCH NEXT FROM curObject INTO @Name, @Owner" p0 {3 I( A& B m! e
END
4 U3 ^- @+ w/ O" e4 F- a) | close curObject, o3 H# P! |: v2 X
deallocate curObject
$ t3 D5 c3 I+ |0 U# J' c% G
5 Y- a" n: U/ X( {* | GO |