SQLServer架構更改
IF OBJECT_ID('dbo.upx_changeSchema') IS NOT NULL BEGIN DROP PROC dbo.upx_changeSchema; END; GO CREATE PROC dbo.upx_changeSchema(@old_schema VARCHAR(200), @new_schema VARCHAR(200)) AS BEGIN DECLARE @objName VARCHAR(200); DECLARE csr CURSOR FOR SELECT NAME FROM sys.objects WHERE TYPE IN ('U', 'V', 'P') AND SCHEMA_NAME([SCHEMA_ID]) = @old_schema; OPEN csr; FETCH NEXT FROM csr INTO @objName; WHILE (@@FETCH_STATUS = 0) BEGIN EXEC('ALTER SCHEMA ' + @new_schema + ' TRANSFER ' + @old_schema + '.' + @objName); FETCH NEXT FROM csr INTO @objName; END CLOSE csr; DEALLOCATE csr; END; GO -- 執行存儲過程 EXEC dbo.upx_changeSchema 'dbo', 'lc001';
本文來自博客園,作者:CelonY,轉載請注明原文鏈接:http://www.rzrgm.cn/CelonY/p/19058470
浙公網安備 33010602011771號