1、先说说如何在SQL 2005中批量修改存储过程的架构。
执行以下SQL,将执行结果拷贝出来,批量执行既可。
1
SELECT
'
ALTER SCHEMA dbo TRANSFER
'
+
s.Name
+
'
.
'
+
p.Name
2
3 FROM sys.Procedures p INNER JOIN sys.Schemas s on p.schema_id = s.schema_id
4
5 WHERE s.Name = ' 旧的架构名称 '
6
2
3 FROM sys.Procedures p INNER JOIN sys.Schemas s on p.schema_id = s.schema_id
4
5 WHERE s.Name = ' 旧的架构名称 '
6
2、再说说如何在SQL 2005中批量修改表的架构。
执行以下SQL,将执行结果拷贝出来,批量执行既可。
Code
1declare @name sysname
2declare csr1 cursor
3for
4select TABLE_NAME from INFORMATION_SCHEMA.TABLES
5open csr1
6
7FETCH NEXT FROM csr1 INTO @name
8while (@@FETCH_STATUS=0)
9BEGIN
10SET @name='旧的架构名称.' + @name
11
12print 'ALTER SCHEMA dbo TRANSFER ' + @name
13fetch next from csr1 into @name
14END
15CLOSE csr1
16DEALLOCATE csr1
17
1declare @name sysname
2declare csr1 cursor
3for
4select TABLE_NAME from INFORMATION_SCHEMA.TABLES
5open csr1
6
7FETCH NEXT FROM csr1 INTO @name
8while (@@FETCH_STATUS=0)
9BEGIN
10SET @name='旧的架构名称.' + @name
11
12print 'ALTER SCHEMA dbo TRANSFER ' + @name
13fetch next from csr1 into @name
14END
15CLOSE csr1
16DEALLOCATE csr1
17