郁闷的数据库
今天转移数据库,发现SQL2005如果你还原备份数据,而数据不是在还原的机子上备份出来的就会出现用户名混乱,郁闷了一早上,送批量修改程序给和我一样郁闷的人。
使用方法:ctrl+A结果后复制到新的查询窗口执行
批量修改存储过程构架:
注:以上内容不是原创,我也是看到了记下来的
使用方法:ctrl+A结果后复制到新的查询窗口执行
批量修改存储过程构架:
SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + p.Name
FROM sys.Procedures p INNER JOIN sys.Schemas s on p.schema_id = s.schema_id
WHERE s.Name = 'old schema name'
批量修改表构架:FROM sys.Procedures p INNER JOIN sys.Schemas s on p.schema_id = s.schema_id
WHERE s.Name = 'old schema name'
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
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
注:以上内容不是原创,我也是看到了记下来的