How To Find A Table In SQL Server

As an SQL developer, I often received requirements for specific tables in my SQL server database. Since I belong to a large organization with massive data, locating a particular table is pretty challenging. So, I have identified a few approaches to do this job efficiently. In this article, I will discuss all the strategies for finding tables in SQL servers.

Table of Contents

Approach-1: Using INFORMATION_SCHEMA.TABLES

We can use the INFORMATION_SCHEMA.TABLES to do this job. Let us execute the below query.

Check out: How To Delete All Records From A Table In SQL Server

Case-1: Listing all the tables

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

After executing the above query, I got the expected output, as shown in the screenshot below.

Case-2: Listing specific table

You can use the query below to get a particular table from your database. The query will list all the tables that include the product in their name.

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME LIKE '%Product%'

After executing the above query, we got the expected output, as shown in the screenshot below.

Check out How To Duplicate A Table In SQL Server

Case-3: Listing tables with specific columns

You can execute the query below, listing all the tables containing the column “ProductName.”

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'ProductName'

After executing the above query, I got the expected output below.

Check out: How to Export SQL Server Table to CSV

Approach-2: Using sp_tables Stored Procedure

You can also use the sp_tables Stored Procedure to find the table name in SQL. You can execute the below query to get the lists of tables that include the name Product.

EXEC sp_tables @table_name = N'%Product%'

After executing the above query, I got the expected output, as shown in the screenshot below.

Conclusion

Knowing the quick approach to finding a table in an SQL server is crucial when you have an extensive database with many tables. You can check out the above methods and decide what suits you best.

You may also like following the articles below.

Grey

Grey is a highly experienced and certified database expert with over 15 years of hands-on experience in designing, implementing, and managing complex database systems. Currently employed at WEX, USA, Grey has established a reputation as a go-to resource for all things related to database management, particularly in Microsoft SQL Server and Oracle environments. He is a Certified Microsoft SQL Server Professional (MCSE: Data Management and Analytics) and Oracle Certified Professional (OCP), with Extensive database performance tuning and optimization knowledge and a proven track record in designing and implementing high-availability database solutions.