SQLite doesn’t have a SHOW TABLES statement like MySQL and MariaDB have, but it does have a similar command.
In SQLite, you can use the .tables command to show a list of tables. You can alternatively use the table_list pragma to do the job.
Tag: tables
4 Ways to List All Tables in a MySQL Database
Below are four ways to list out the tables in a MySQL database using SQL or the command line.
Continue readingSQL Server SHOW TABLES Equivalent
Every now and then I find myself typing SHOW TABLES in SQL Server, expecting to get a list of tables.
That would make perfect sense if I was using MySQL or MariaDB. But SQL Server/T-SQL doesn’t have a SHOW TABLES statement like MySQL or MariaDB, so it never works. And I keep forgetting. But fortunately, SQL Server does have alternatives.
Here are five options for getting a list of tables in SQL Server. These can be used whenever you’re trying to find that elusive SHOW TABLES statement in SQL Server.
MySQL TABLE Statement
In MySQL, the TABLE statement returns rows and columns of the given table.
The TABLE statement is similar to the SELECT statement, and it can be used as a kind of shorthand version of the SELECT statement.
The TABLE statement was introduced in MySQL 8.0.19.
SHOW TABLES in MySQL
In MySQL, SHOW TABLES is an administrative statement that lists the non-TEMPORARY tables and views in a given database.
SHOW TABLES only lists the tables and views for which you have privileges.
SHOW TABLES in MariaDB
In MariaDB, SHOW TABLES is an administrative statement that lists the non-TEMPORARY tables, sequences and views in a given database.
3 Ways to Create a Table if it Doesn’t Already Exist in Oracle
The very useful CREATE TABLE IF NOT EXISTS syntax was finally introduced in Oracle Database – Oracle Database 23c to be precise. This syntax allows us to run a CREATE TABLE statement without getting an error if the table already exists.
Earlier versions of Oracle don’t support the IF NOT EXISTS clause, and so if we want to avoid any nasty errors resulting from trying to create a table that already exists, we need to do a bit of extra work.
4 Ways to Check if a Table Exists Before Dropping it in SQL Server (T-SQL)
Dropping a table in SQL easy. You simply use DROP TABLE myTable where myTable is the name of the table you want to drop. You can use this method to drop a table in SQL Server via T-SQL script.
But you’ll get an error if the table doesn’t actually exist. That is, unless you check for the existence of the table first.
Below are four ways of using T-SQL to check whether the table exists before dropping it.
Continue readingHow to Create a Table Only if it Doesn’t Exist in SQLite
In SQLite, you can use the IF NOT EXISTS clause of the CREATE TABLE statement to check whether or not a table or view of the same name already exists in the database before creating it.
Creating a table without this clause would normally result in an error if a table of the same name already existed in the database. But when using the IF NOT EXISTS clause, the statement has no effect if a table already exists with the same name.
2 Ways to Create a Table if it Doesn’t Exist in SQL Server
T-SQL doesn’t include the IF NOT EXISTS clause with its CREATE TABLE statement, like some other DBMSs do.
Therefore, if we want to check for the existence of the table before we create it in SQL Server, we need to use other methods.
Continue reading