How to display the tables containing particular strings in SQL?
- How-Tos FAQs
- December 17, 2018
Ever wondered if there was an easier way to display tables containing particular strings in SQL?
In this short how-to guide, we’ll show you:
- A no-code way to search out your tables based on certain strings or column names.
- Four other methods to display the tables containing particular strings in SQL.
Let’s dive in!
1. A no-code way to search your tables based on certain strings or column names.
Umm..you must have guessed it by now.
The answer is Datameer ; a 2-second, no-code way to search your tables and columns without writing a single line of code.
Datameer is an all-in-one data transformation tool that sits on Snowflake, It brings together your entire team — data engineers, analytics engineers, analysts, and data scientists — on a single platform to collaboratively transform and model data directly in Snowflake.
See demo below:
2. Four other methods to display the tables containing particular strings in SQL.
In SQL Server, we have four different ways to list all the tables in a database.
SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'BASE TABLE'
AND table_name LIKE '%student%'
SELECT name
FROM sys.tables
WHERE name LIKE 'student%'
SELECT name
FROM sysobjects
WHERE xtype = 'U'
AND name LIKE '%student'Oracle
In Oracle, we have three different ways to list all the tables in a database.
-- This returns all the tables in the database system containing string ‘student’ in the name of the table.
SELECT table_name
FROM dba_tables
WHERE table_name LIKE '%student%'
-- This returns all the tables which are accessible to the current user and table name starting with ‘student’
SELECT table_name
FROM all_tables
WHERE table_name LIKE 'student%'
-- This returns all the tables which are created by the current user and table name ending with ‘student’
SELECT table_name
FROM user_tables
WHERE table_name LIKE '%student'MySQL:
In MySQL, we can use the below query to list all the tables in the server.
-- Lists all the tables in all databases containing string ‘student’ in the name of the table.
SELECT table_name
FROM information_schema.tables
WHERE table_type='BASE TABLE'
AND table_name LIKE '%student%'
-- Lists all the tables whose name starts with ‘student’ in a particular database
SELECT table_name
FROM information_schema.tables
WHERE table_type='BASE TABLE'
AND table_schema = 'your_database_name'
AND table_name LIKE '%student'