Forum Discussion

Dlkns's avatar
Dlkns
Copper Contributor
Oct 08, 2024

how to export all tables from database

I'm recently started working in Microsoft Synapse and exploring the templates from the gallery available in Synapse "Database templates" and want to export all tables e.g. Automotive. I’ve tried using the DESCRIBE command, but it only gives information about a single table. How can I write a SQL query to export all tables from the database template and export it to CSV? 

 

Is there a specific system view or query I should use in Synapse to achieve this?

 

Any help would be appreciated.

Thanks in advance!

1 Reply

  • petevern's avatar
    petevern
    Brass Contributor
    Jan 25, 2025

    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.

Resources