SQL Server2005可以使用系统存储过程sp_changeobjectowner更改数据库对象的所有者。
sp_changeobjectowner '对象名(包括架构名)','新架构名'
批量修改请用:
方法一:使用游标
declare @name sysname
declare csr1 cursor
forselect 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
如何在SQL 2005中批量修改存储过程的架构。
执行以下SQL,将执行结果拷贝出来,批量执行既可。
SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + p.Name
FROM sys.Procedures p INNER JOIN sys.Schemas s on p.schema_id = s.schema_id
WHERE s.Name = '旧的架构名称'
sp_changeobjectowner '对象名(包括架构名)','新架构名'
批量修改请用:
方法一:使用游标
declare @name sysname
declare csr1 cursor
forselect 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
如何在SQL 2005中批量修改存储过程的架构。
执行以下SQL,将执行结果拷贝出来,批量执行既可。
SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + p.Name
FROM sys.Procedures p INNER JOIN sys.Schemas s on p.schema_id = s.schema_id
WHERE s.Name = '旧的架构名称'