Snowflake how to loop over all tables in a schema and get total record count and max created at

I am getting the following error when trying the script below:

ERRORS: syntax error line 17 at position 16 unexpected 'IMMEDIATE'. syntax error line 17 at position 26 unexpected 'sql_cmd'. (line 29)

--Snowflake how to loop over all tables in a schema and get total record count and max_target_changed_on

-- Create a temporary table to store the results
CREATE OR REPLACE TEMPORARY TABLE TEMP_TARGET_TABLE (
    TABLE_NAME STRING,
    ROW_COUNT INTEGER,
    MAX_TARGET_CHANGED_ON TIMESTAMP
);

DECLARE
    table_name STRING;
    row_count INTEGER;
    sql_cmd STRING;
    sql_cmd2 STRING;
    max_target_changed_on TIMESTAMP;
    table_cursor CURSOR FOR (
        SELECT TABLE_NAME 
        FROM INFORMATION_SCHEMA.TABLES 
    );

BEGIN
    FOR record IN table_cursor DO
        LET sql_cmd = 'SELECT COUNT(*) FROM ' || TABLE_NAME;
        EXECUTE IMMEDIATE sql_cmd INTO row_count;

        LET sql_cmd2 = 'SELECT MAX(TARGET_CHANGED_ON) FROM ' || TABLE_NAME;
        EXECUTE IMMEDIATE sql_cmd2 INTO max_target_changed_on;
        
        -- Insert the table name and row count into the temporary table
        INSERT INTO TEMP_TARGET_TABLE (TABLE_NAME, ROW_COUNT, MAX_TARGET_CHANGED_ON)
        VALUES (record.TABLE_NAME, row_count, max_target_changed_on);
    END FOR;

    SELECT * FROM TEMP_TARGET_TABLE
END;

SCRIPT:

I was expecting to get a result set with one record per table, with total records and max target changed on values.

If I take your SQL and simplify, and fix errors, I end up with a starting point of:

DECLARE
    table_name text;
    row_count INTEGER;
    table_cursor CURSOR FOR (
        SELECT TABLE_CATALOG||'.'||table_schema||'.'||TABLE_NAME as table_full_name
        FROM INFORMATION_SCHEMA.TABLES 
    );

BEGIN
    FOR record IN table_cursor DO
        table_name := record.table_full_name;
        SELECT COUNT(*) into row_count FROM IDENTIFIER(:table_name);


    END FOR;

    return row_count;
END;

this correctly loops across the tables as can be seen in the query history:

so now from there it can be extended, to insert which can be an inline command also.