How to get table names in given pattern in Sql Server

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. Like

sp_tables '%cust%'

Result :

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%'


Result:

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%'

Result :