SQL Server2005可以使用系统存储过程sp_changeobjectowner更改数据库对象的所有者。
sp_changeobjectowner '对象名(包括架构名)','新架构名'
批量修改请用:
方法一:使用游标
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
方法二:使用系统存储过程sp_MSforeachtable
EXEC sp_MSforeachtable @command1="EXEC SP_ChangeObjectOwner '?','新架构名'"
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/htl258/archive/2009/06/10/4258616.aspx
sp_changeobjectowner '对象名(包括架构名)','新架构名'
批量修改请用:
方法一:使用游标
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
方法二:使用系统存储过程sp_MSforeachtable
EXEC sp_MSforeachtable @command1="EXEC SP_ChangeObjectOwner '?','新架构名'"
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/htl258/archive/2009/06/10/4258616.aspx