Listing tables in a MySQL database

Posted by Kyle Hankinson June 24th, 2020


Listing tables

Have you ever needed to see the tables in a MySQL database? MySQL provides a simple and easy query for listing them.

SHOW TABLES

The SHOW TABLES command is the quickest and easiest way to get the list of tables available. You can run this from the MySQL CLI or from any MySQL GUI.

Example

In the example northwind database, running SHOW TABLES will give you results similar to the following:

+--------------------------------+
| Tables_in_northwind            |
+--------------------------------+
| Alphabetical list of products  |
| Categories                     |
| Category Sales for 1997        |
| Current Product List           |
| Customer and Suppliers by City |
| CustomerCustomerDemo           |
| CustomerDemographics           |
| ...                            |
| Products by Category           |
| Quarterly Orders               |
| Region                         |
| Sales Totals by Amount         |
| Sales by Category              |
| Shippers                       |
| Summary of Sales by Quarter    |
| Summary of Sales by Year       |
| Suppliers                      |
| Territories                    |
+--------------------------------+

Tables Only?

By default when running SHOW TABLES you will receive a list of both Tables and Views. If you want to narrow down to just showing objects which are a table, you will want the following:

SHOW FULL TABLES WHERE Table_Type = 'BASE TABLE'

Similar, if you want just views then you will want:

SHOW FULL TABLES WHERE Table_Type = 'VIEW'

Additional options

There are a few other options available with the SHOW TABLES command. You can find additional details at the official MySQL Documentation.


Tags: MySQL

MySQL with Swift and Objective-C

Posted by Kyle Hankinson June 24th, 2020


Prefix

In general its a better idea to use a webservice and to keep direct database connections out of your mobile or desktop apps. If you are still looking to connect to a MySQL (or MariaDB) database, MariaDBKit is an iOS and macOS compatible framework for making a connection.

Getting started

MariaDBKit supports Carthage. The easiest way to get started is by adding the following to a Cartfile in your projects root directory. You will want to add the following to your Cartfile.

github "hankinsoft/MariaDB"

Then run 'carthage update' from terminal:

Once completed, you now have a framework available for use in both iOS and macOS apps, which you can link to any Objective-C or Swift project. Switch to the 'Build Phases' tab of your target and either drag the framework in, or add it by using the 'Plus' button, followed by navigating to the Carthage build output directory.

Code

Now comes the fun part. The following sample code allows a connection to the MySQL database, along with running a query and enumerating the results.

import MariaDBKit

let client = MariaDBClient()

do {
    try client.connect("sqlprostudio-mysql.csyg8tkobue6.us-west-2.rds.amazonaws.com", username: "sqlprostudio-ro", password: "password123", database: "northwind")

    var error : NSError?
    let results = client.executeQuery("SHOW TABLES;", error: &error)

    if let theError = error {
        print(theError)
        return
    }

    while(results.next(&error)) {
        print(results.object(forColumnIndex: 0))
    }
}
catch {
    print(error)
}
@import MariaDBKit;

MariaDBClient * client = [[MariaDBClient alloc] init];
NSError * error = nil;

BOOL success = [client connect: @"sqlprostudio-mysql.csyg8tkobue6.us-west-2.rds.amazonaws.com"
                      username: @"sqlprostudio-ro"
                      password: @"password123"
                      database: @"northwind"
                         error: &error];

if(!success || error)
{
    NSLog(@"Failed to connect to MySQL Server.");
    return;
} // End of we had an error

MariaDBResultSet * results = [client executeQuery: @"SHOW TABLES;"
                                            error: &error];

if(error)
{
    NSLog(@"Error: %@", error.localizedDescription);
    return;
}

while([results next: &error])
{
    NSLog(@"%@", [results objectForColumnIndex: 0]);
} // End of results loop

The above code runs the query SHOW TABLES, which gives us a single column result containing the table names. While in this cases we can assume that we are receive a single column, you can also get a list of column names from a result. You might want to do this if you were running a wildcard query against a table (such as SELECT * FROM myTable). The following code example will run a query against the Employees table and give us a list of column names.

import MariaDBKit

let client = MariaDBClient()

do {
    try client.connect("sqlprostudio-mysql.csyg8tkobue6.us-west-2.rds.amazonaws.com", username: "sqlprostudio-ro", password: "password123", database: "northwind")

    var error : NSError?
    let results = client.executeQuery("SELECT * FROM Employees;", error: &error)

    if let theError = error {
        print(theError)
        return
    }

    // Output the number of columns along with the list of columns themselves.
    print(String(format: "Table has %ld columns.\r\n%@.", results.columnNames.count,
          results.columnNames.joined(separator: ",")))
}
catch {
    print(error)
}
@import MariaDBKit;

MariaDBClient * client = [[MariaDBClient alloc] init];
NSError * error = nil;

BOOL success = [client connect: @"sqlprostudio-mysql.csyg8tkobue6.us-west-2.rds.amazonaws.com"
                      username: @"sqlprostudio-ro"
                      password: @"password123"
                      database: @"northwind"
                         error: &error];

if(!success || error)
{
    NSLog(@"Failed to connect to MySQL Server.");
    return;
} // End of we had an error

MariaDBResultSet * results = [client executeQuery: @"SHOW TABLES;"
                                            error: &error];

if(error)
{
    NSLog(@"Error: %@", error.localizedDescription);
    return;
}

NSLog(@"Table has %ld columns.\r\n%@.", results.columnNames.count, [results.columnNames componentsJoinedByString: @","]);

Up Next

For the next blog post, I plan on creating a small SwiftUI wrapper around this. It will allow a user to enter a host, username and password to connect. Then once connected, a text entry where a user can enter a query and finally a results grid which will display the results of the query.


Tags: Swift Objective-C MySQL