how to export all tables from database

Synapse Analytics provides system views, INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS to query metadata about tables and columns in a database.

Query all tables:

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';

Query all tables and columns

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS 

In Synapse studio you can export the results to an CSV file.

If it needs to be recurring, I would suggest using a PySpark notebook or Azure Data Factory.