更改SQL2005用户表的所有者,把是用户名改成dbo
1、在SQL Server 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 = '旧的架构名称'
第二步、把第一步执行得到的结果全部再执行一次
2、在SQL Server 2005中批量修改存储过程的架构,执行以下SQL，将执行结果拷贝出来，批量执行既可。
第一步、执行下面代码：
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
print 'Alter SCHEMA dbo TRANSFER ' + @name
fetch next from csr1 into @name
END
CLOSE csr1
DEALLOCATE csr1
------------执行结果如：----------
Alter SCHEMA dbo TRANSFER cwb4129.tbl_sellbuy2
Alter SCHEMA dbo TRANSFER cwb4129.tbl_outsourcing
Alter SCHEMA dbo TRANSFER cwb4129.tbl_sellbuy
Alter SCHEMA dbo TRANSFER cwb4129.tbl_directory
Alter SCHEMA dbo TRANSFER cwb4129.tbl_company2
Alter SCHEMA dbo TRANSFER cwb4129.tbl_count
Alter SCHEMA dbo TRANSFER cwb4129.tbl_company
Alter SCHEMA dbo TRANSFER cwb4129.tbl_code
Alter SCHEMA dbo TRANSFER cwb4129.tbl_ad
Alter SCHEMA dbo TRANSFER cwb4129.smt_xxnewssort_vip
Alter SCHEMA dbo TRANSFER cwb4129.SMT_xxnewssort
第二步、把第一步执行得到的结果全部再执行一次
本文摘自：万广互联 (www.wang0214.com)
页:
[1]