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

时间:2022-07-01 07:19:48

有时候折腾数据库的用户名,导入导出的时候忘了选所有者了,这时候这个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

... ... ...

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

 

 

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