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';