SQLSERVER 修改实例名以及架构信息
时间：2021-07-01 10:21:17
帮助过：24人阅读
DECLARE @table SYSNAME;
DECLARE @schema SYSNAME;
DECLARE @new_schema SYSNAME;
SELECT @schema = ‘oldname‘ --原有属主
SELECT @new_schema = ‘newname‘ --新属主
DECLARE csr CURSOR FOR
SELECT o.name, u.name FROM sys.objects o
INNER JOIN sys.schemas u ON o.schema_id =
u.schema_id
WHERE o.type in(
‘u‘,
‘p‘,
‘v‘,
‘tf‘,
‘fn‘,
‘if‘) AND u.name =
@schema
OPEN csr;
FETCH NEXT FROM csr INTO @table, @schema;
WHILE @@FETCH_STATUS =
0
BEGIN
exec (‘ALTER SCHEMA ‘ + @new_schema +
‘ TRANSFER [‘ + @schema +
‘].[‘ + @table +
‘]‘);
FETCH NEXT FROM csr INTO @table, @schema;
END
CLOSE csr
DEALLOCATE csr ;
自己写的 改天验证一下.
SQLSERVER 修改实例名以及架构信息
标签：join 执行 select 数据库实例 str 用户名 exe ext sql
