A short time ago, we explored Some SELECT Queries You Must Know. These included determining the lowest and highest value for a column, as well as grouping results by category. Today's blog presents a couple more queries, along with a tip to make your queries almost write themselves!
Get All User Created Tables
These include tables that are part of user created databases, that is to say, that are not part of system database schemas. The exact syntax varies by vendor, but here are a couple of examples to give you the idea.
In SQL Server, this simple one-liner will do the job:
SELECT NAME FROM sys.objects WHERE TYPE='U'
MySQL's syntax is a bit more wordy because you have to specify the system databases in order to omit their tables:
SELECT * from information_schema.tables
WHERE table_schema not in ('information_schema', 'mysql', 'performance_schema')
ORDER BY table_schema, table_name;
So why would you want to query user tables? In addition to table names, the MySQL query returns a great deal of useful information about each table, including the number of rows, the storage engine, their size, the last auto_increment value, and more!
If you only want the table names in MySQL, that's easily done. You can narrow down the list using the WHERE clause, or, you can issue the following command:
SHOW FULL TABLES IN [database_name] WHERE TABLE_TYPE LIKE 'BASE TABLE';
Get All View Names
Again, the exact syntax varies by vendor, but a couple of examples will provide a good starting point.
Here's the SQL Server syntax:
SELECT * FROM sys.views
In MySQL we can narrow down the list to views by limiting the TABLE_TYPE to 'VIEW'. We still have to exclude the sys database as it contains a number of views:
SELECT * FROM information_schema.`TABLES`
WHERE TABLE_TYPE = 'VIEW'
AND table_schema != 'sys';
Here are the results in Navicat Premium:
Looking for views of a specific database? You can just change the WHERE clause to:
AND TABLE_SCHEMA LIKE '[database_name]'
The following command will also work:
SHOW FULL TABLES IN [database_name] WHERE TABLE_TYPE LIKE 'VIEW';
That will return the view names and their type, which is always "view":
General Tip: Using Table Aliases
Writing SQL queries is an art as much as a science. There are some good habits that you can develop that will pay dividends in productivity and/or ease of writing. For example, table (or SQL) aliases are used to give a table, or a column in a table, a temporary name that only exists for the duration of the query. Aliases may be employed to make column names more readable and less error prone.
All you need to do is include the "AS [alias_name]" after the table name in the FROM clause:
SELECT column_name(s)
FROM table_name AS alias_name;
Aliases really earn their keep when you use a Query Editor, like Navicat's. Suppose that we want to select some fields from the actor table. First, we would leave the column list empty and we would enter the FROM clause, complete with a table alias:
SELECT
FROM actor as a
Now, when we enter our shorter table alias, Navicat presents an auto-complete list with all the table columns:
Writing queries in this way is not only faster, but it eliminates the chance of misspelling a column!
Conclusion
In today's blog, we learned a couple of queries and a tip to make our SELECTs almost write themselves using Navicat Premium as the database client. Navicat helps you code fast with Code Completion and customizable Code Snippets by getting suggestions for keywords and stripping the repetition from coding. You can try it for 14 days completely free of charge for evaluation purposes.
