Query below finds tables which names start with specific prefix, e.g. tables with names starting with 'C' in the 'Sales' database.
SELECT DatabaseName, TableName FROM DBC.TablesV WHERE TableKind = 'T' AND TableName LIKE 'C%' AND DatabaseName = 'Sales' ORDER BY TableName;
- DatabaseName - name of database (redundant as it should be exactly the same as provided)
- TableName - name of found table
- One row represents one table
- Scope of rows: all found tables in 'Sales' database
- Ordered by table name