how to export metadata of tables and columns to AWS S3?

There are several ways to export table and column metadata to Amazon S3, depending on your specific use case and the AWS services you're using:

For SageMaker Unified Studio users: You can export asset metadata as an Apache Iceberg table through Amazon S3 Tables. This is enabled using the PutDataExportConfiguration API action. The error you're encountering might be because this feature isn't available in the ap-southeast-1 region, as Amazon SageMaker Catalog features are only available in specific AWS regions. The correct command syntax is:

aws datazone put-data-export-configuration --domain-identifier dzd-440699i00ezy21 --region us-east-2 --enable-export

For database migrations: If you're working with databases like Amazon RDS for SQL Server, you can use AWS Database Migration Service (DMS) to export tables to an S3 bucket. This involves:

1. Setting up the necessary IAM roles for access between your database and S3
2. Creating source and target endpoints in AWS DMS
3. Setting up a replication instance
4. Creating and running a migration task to export the data to S3

For database schema assessment: AWS DMS Schema Conversion CLI allows you to export metadata model assessments to S3 using the export-metadata-model-assessment command. This generates reports in PDF or CSV format that include object metadata and migration recommendations.

For general data inventory export: AWS Application Migration Service provides an option to export your data inventory to an S3 bucket through its console interface. Navigate to the Export section, select "Export to S3 bucket," browse and select your target S3 bucket, and click Export.

For large-scale data processing: You can also build custom solutions using services like AWS Step Functions Distributed Map to process data objects and export structured metadata to Amazon S3 Tables, which can then be queried using Amazon Athena or other BI tools.

If SageMaker Unified Studio's export capability isn't available in your region, consider using one of these alternative approaches based on your specific requirements.
Sources
Exporting asset metadata - Amazon SageMaker Unified Studio
Exporting your data inventory to an S3 bucket - Application Migration Service
Assess and migrate your database using AWS DMS Schema Conversion CLI | AWS Database Blog
Export Amazon RDS for SQL Server tables to an S3 bucket by using AWS DMS - AWS Prescriptive Guidance