批量修改数据库表的架构sql

有时候折腾数据库的用户名，导入导出的时候忘了选所有者了，这时候这个sql就用上了，通过测试，

 

在SQL 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
... ... ...

然后将这个结果在执行一遍即可。

 

 

还有一个是直接修改数据库的所有者，重新创建一下，一下想不起来了，后续更新……

 

转载于:https://www.cnblogs.com/lilo202/archive/2012/03/10/2389087.html