1.SQL Server中批量更新Object的Owner 2.附加数据库

来自：

http: // www.pconline.com.cn / pcjob / other / data / others / 0611 / 903723_ 3 .html

 

CREATE   PROCEDURE  dbo.User_ChangeObjectOwnerBatch( @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
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
--  select @name,@NewOwner,@OldOwner
FETCH   NEXT   FROM  curObject  INTO   @Name ,  @Owner
END
close  curObject
deallocate  curObject
GO  
--test
exec  User_ChangeObjectOwnerBatch  ' BMS ' , ' dbo '

 

2、附加数据库：

exec   sp_attach_db   @dbname    =    '' hw '' , @filename1    =    '' c:\hw_data.mdf '' , @filename2    =    '' c:\hw_log.ldf ''