Redshift query to generate UNLOAD and COPY statements

Below is a statement that can be used to generate a list of commands to copy all tables from Redshift to S3.  Please note that you'll need to substitute YOUR_BUCKET_NAME, YOUR_ACCOUNT_ID and YOUR_ROLE placeholders with the values appropriate for your environment.

SELECT
' UNLOAD (''SELECT * FROM ' ||
"table" ||
''')' ||
' TO ''s3://YOUR_BUCKET_NAME/' ||
"table" ||
'/''' ||
' iam_role 'arn:aws:iam::YOUR_ACCOUNT_ID:role/YOUR_ROLE''' ||
' delimter ''|'' addquotes escape allowoverwrite;'
FROM SVV_TABLE_INFO;

In a situation where you want use to copy data into a different redshift cluster, the following will create the copy commands that can be used to copy data from S3 to Redshift.  Please note that you'll need to substitute YOUR_BUCKET_NAME, YOUR_ACCOUNT_ID and YOUR_ROLE placeholders with the values appropriate for your environment.

SELECT
' COPY ' ||
"table" ||
' FROM ''s3://YOUR_BUCKET_NAME/' ||
"table" ||
'/''' ||
' iam_role ''arn:aws:iam::YOUR_ACCOUNT_ID:role/YOUR_ROLE''' ||
' delimiter ''|'' removequotes escape '';'
FROM SVV_TABLE_INFO;