bluesailor散记

批量更改存储过程的所有者
declare @name sysname
declare csr1 cursor
for select TABLE_NAME from INFORMATION_SCHEMA.TABLES
open csr1
FETCH NEXT FROM csr1 INTO @name
while (@@FETCH_STATUS=0)
BEGIN
SET @name='原名字.'+@name
EXEC SP_ChangeObjectOwner @name, '新名字'
fetch next from csr1 into @name
END
CLOSE csr1
DEALLOCATE csr1

删除所有表。
exec sp_msforeachtable 'drop table ?'

删除所有存储过程。
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + name From sysobjects WHERE xtype='P' and status>=0
select @str = 'drop proc ' + substring(@str,2,8000)
select @str
exec (@str)

==================================
SQL code--改过程的所有者：

cREATE PROCEDURE dbo.ChangeObjectOwner
@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
以SA登陆查询分析器 ，选中你要的数据库
执行exec ChangeObjectOwner '原所有者','dbo'