Tag Archives: sp_tables

How to find all the tables with name like a 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.

sp_tables '%cust%'

RESULT:
Table Name Like in Sql Server Using sp_tables

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:
Table Name Like in Sql Server Using sys.tables

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:
Table Name Like in Sql Server Using information_schema.tables