Many a times I come across a scenario where I will be remembering only part of the table name and need to find the complete table name. Traversing through hundreds of tables in the database and finding the exact table is boring, tedious time consuming job. In such scenarios we can use on of the below three approaches, I always use the first one as it is easy for me to remember. Let me know which approach which you use and reason for the same.
To demo this create a sample database with three tables by the below script:
CREATE DATABASE SqlHintsDemoDB GO USE SqlHintsDemoDB GO CREATE TABLE dbo.Customers (CustId INT, Name NVARCHAR(50)) CREATE TABLE dbo.CustomerOrders (OrderId INT, CustId INT) CREATE TABLE dbo.Employee(EmpId INT, Name NVARCHAR(50)) GO
Approach 1: Using sp_tables
We can use sp_tables statement like below to find all the tables in the database whose name contains the word cust in it.
Approach 2: Using sys.Tables
We can use sys.tables catalog view like below to find all the tables in the database whose name contains the word cust in it.
SELECT * FROM sys.Tables WHERE name LIKE '%cust%'
Approach 3: Using information_schema.tables
We can use information_schema.tables information schema view like below to find all the tables in the database whose name contains the word cust in it.
SELECT * FROM information_schema.tables WHERE table_name LIKE '%cust%'
6 thoughts on “How to find all the tables with name like a given pattern in Sql Server?”
I love your efforts in educating the community.
You have got good material how about making Youtube video?
How to use this with multiple search sub-strings %Cust% or/and %Ord% for example?
How to print all the customer names that consists of word “more” irrespective of its position in that name, for example, it should print moree, Amoree, Dellmore, Seamore
It works for me Thanks.
Big help! Thank you for sharing your knowledge.
Thanks Brain. I’m glad you found it useful.