1 SET @OldSchema = '旧的架构名'
2 SET @NewSchema = '新的架构名'
3
4 这两个地方的架构名。
5
6 这个是在网上流传例子的基础上改的。
7
8 DECLARE @OldSchema NVARCHAR(50),@NewSchema NVARCHAR(50)
9 SET @OldSchema = 'sysuren'
10 SET @NewSchema = 'dbo'
11 declare @cmd varchar(2000)
12 declare csr1 cursor
13 for
14 SELECT 'ALTER SCHEMA ' + @NewSchema + ' TRANSFER ' + s.Name + '.' + f.Name
15 FROM sys.objects f inner join sys.Schemas s
16 ON f.schema_id = s.schema_id
17 WHERE f.type IN ('U','P','FN', 'IF', 'TF') -- U 用户表 P 存储过程 FN 标量函数 IF 聚合函数 TF 表值函数
18 and s.Name = @OldSchema
19
20 open csr1
21 fetch next from csr1 into @cmd
22 while(@@FETCH_STATUS=0)
23 begin
24 exec(@cmd)
25 fetch next from csr1 into @cmd
26 end
27 close csr1
28 deallocate csr1
