Stored procedure in snowflake to convert all views to table

I have created a stored procedure that can convert all views from a schema into tables in another schema but it simply does not run in Snowflake.

CREATE OR REPLACE PROCEDURE COPY_VIEWS_TO_TABLES()
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
    SOURCE_SCHEMA STRING;       
    TARGET_SCHEMA STRING;       
    CURRENT_VIEW STRING;        
    CREATE_TABLE_SQL STRING;    

BEGIN
    -- Assign values to the source and target schemas
    SOURCE_SCHEMA := 'SOURCE.STAGE';
    TARGET_SCHEMA := 'TARGET.STAGE.TABLES';

    -- Iterate through all views in the source schema
    FOR VIEW_NAME IN (
        SELECT TABLE_NAME
        FROM INFORMATION_SCHEMA.VIEWS
        WHERE TABLE_SCHEMA = SOURCE_SCHEMA
    )
    DO
        -- Assign the current view name
        CURRENT_VIEW := VIEW_NAME;

        -- Construct the SQL statement to create a table from the view
        CREATE_TABLE_SQL := 
            'CREATE TABLE "' || TARGET_SCHEMA || '"."' || CURRENT_VIEW || '" AS 
             SELECT * FROM "' || SOURCE_SCHEMA || '"."' || CURRENT_VIEW || '"';

        -- Execute the dynamic SQL statement
        EXECUTE IMMEDIATE CREATE_TABLE_SQL;
    END FOR;

    RETURN 'All views have been copied successfully!';
END;
$$;

Please advise.

If I change your SQL to:

CREATE OR REPLACE PROCEDURE COPY_VIEWS_TO_TABLES()
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
    SOURCE_SCHEMA STRING;       
    TARGET_SCHEMA STRING;       
    CURRENT_VIEW STRING;        
    CREATE_TABLE_SQL STRING;    
    c1 CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'SO';
BEGIN
    -- Assign values to the source and target schemas
    
    SOURCE_SCHEMA := 'SO.SO';
    TARGET_SCHEMA := 'SO._TO';

    OPEN c1;
    -- Iterate through all views in the source schema
    FOR VIEW_NAME IN c1
    DO
        -- Assign the current view name
        CURRENT_VIEW := VIEW_NAME.TABLE_NAME;

        -- Construct the SQL statement to create a table from the view
        CREATE_TABLE_SQL := 
            'CREATE TABLE ' || TARGET_SCHEMA || '."' || CURRENT_VIEW || '" AS 
             SELECT * FROM ' || SOURCE_SCHEMA || '."' || CURRENT_VIEW || '"';

        -- Execute the dynamic SQL statement
        -- for debuging this SQL
        --RETURN :CREATE_TABLE_SQL;
        EXECUTE IMMEDIATE CREATE_TABLE_SQL;
    END FOR;

    RETURN 'All views have been copied successfully!';
END;
$$;

it will copy from db.schema so.so to database.schema so._to

To get it working I run your given SQL, and got the error:

Error:'EXPRESSION_ERROR' on line 14 at position 21 : SQL compilation error:

error line 4 at position 29 (line 45) invalid identifier 'SOURCE_SCHEMA' (line 45)

so I changed the SQL to:

    FOR VIEW_NAME IN (
        SELECT TABLE_NAME
        FROM INFORMATION_SCHEMA.VIEWS
        WHERE TABLE_SCHEMA = :SOURCE_SCHEMA
    )

which gave:

092234 (P0000): Uncaught exception of type 'STATEMENT_ERROR' on line 16 at position 4 : Expression in variant for loop does not evaluate to an array or an object

then I read the FOR doc's and noticed the examples are using a cursor.

so swapped to that, then needed to change CURRENT_VIEW := VIEW_NAME.TABLE_NAME;

Then I changed how you were quoting the database name & schema name, as these should not need to be quoted as "nobody would use special tokens in these, right?"

And I was returning the SQL, so I could run it outside the SP to test it.