Overview

This Snap outputs a list of tables in a database. The Snap will connect to the database, read its metadata, and output a document for each table found in the database. The table names are output in a topological order so that tables with the fewest dependencies are output first. In other words, if table A has a foreign key reference to table B, then table B will be output before A. The ordering is intended to ease the process of replicating a group of tables from one database to another.

• This is a Read-type Snap.
• Works in Ultra Tasks

Prerequisites

A valid account with SELECT privileges on the information_schema database or appropriate privileges to query table metadata.

Known issues

When MySQL execute Snaps (MySQL Execute and MySQL Multi Execute) are followed by MySQL non-execute Snaps, such as MySQL Insert, MySQL Merge, and so on, the following error is displayed when executed:

Table definition has changed, please retry transaction.

This happens due to a known issue in the MySQL Connector. For more information about this issue, see MySQL Bug #65378.

Snap views

Replicating a Subset of Tables

The output of the Table List Snap can be directly used to replicate an entire database. However, if you are only interested in a subset of tables, you can use a Filter Snap to select the table names you are interested in as well as the tables that they reference. For example, given the following diamond-shaped table graph where A depends on B and C and they both depend on D:

       A
      / \
     B   C
      \ /
       D

The TableList will output the following documents:

name=D; dependents=[A, B, C, D]
name=C; dependents=[A, C]
name=B; dependents=[A, B]
name=A; dependents=[A]

So, if you wanted to copy just table 'A' and its dependencies, you can add a Filter Snap with the following expression:

$.dependents.indexOf('A') != -1

The filter will then remove any extra tables that happen to be in the schema.

Snap settings

Examples

• MySQL Table List Example