SQL Server: Search and Find Table by Name

There are several ways to search and find the tables in the SQL Server database by table name or by column name. Below are few of the methods to find table in the database. I’ve used these methods in SQL Server 2014 and they are working good.

I. Find Table By Table Name Querying sys.tables

The most common and simple method to find and list down the tables in a database based on the name of the table or a phrase is by using this simple select query against the system table sys.tables. If you are a sql expert then this will be the first option you will choose.

SELECT 
	* 
FROM 
	sys.tables 
WHERE 
	name 
LIKE 
	'%product%'

II. Find Table By Table Name Using Filter Settings in Object Explores

Another easiest method to find the tables by the table’s name in SQL Server database is to use the filter settings option in the object explorer in SQL Server Management Studio.

1. In the Object Explorer in SQL Server Management Studio, go to the database and expand it.
2. Right Click the Tables folder and select Filter in the right-click menu.
3. Under filter, select Filter Settings. The filter settings window will pop up.
   
4. In the filter settings window against the property Name, you can choose one of the Operator from the list (Equals, Contains, Does not contain) and enter the search phrase (or table name) under Value and then click OK.
   
5. The list of tables under the Tables folder are filtered based on your filter criteria.

III. Find Table From All The Databases By Table Name

There could be a specific scenario to find the table with the same name or phrase of the name from all the databases in the SQL Server. If the SQL Server has less than 10 user databases then it will be easy for you to use one among the above two methods to find the table in the databases separately. But, In case if the SQL Server has hundreds of databases, then searching for the table in each database is difficult and time-consuming. In this scenario you can use the sp_msforeachdb system stored procedure. sp_msforeachdb allows you to execute a command against all the databases in the current SQL Server. You can use this system stored procedure to execute a select command to fetch the list of tables with filter condition from sys.tables against all the databases.

EXEC 
	sys.sp_msforeachdb 
	'SELECT ''?'' DatabaseName, Name FROM [?].sys.Tables WHERE Name LIKE ''%product%'''

IV. Find Table By Column Name Using SQL Query

Another common scenario in the database world is to find the tables having the specific column or the columns containing a phrase in the name. For such a need to find the tables having the column, you can query the sys.tables system table and joining the sys.columns system table. Here is the simple select query to find the list of tables having a column.

SELECT 
	sys.tables.name AS 'Table Name', 
	sys.tables.object_id AS 'Object ID', 
	sys.columns.name AS 'Column Name'
FROM
	sys.tables INNER JOIN sys.columns 
		ON sys.tables.object_id = sys.columns.object_id
WHERE 
	sys.columns.name LIKE '%ProductID%'
ORDER BY 1;

Another method to get the list of tables having the same column is by querying the sys.columns table along with the OBJECT_NAME built-in meta data function instead of using sys.column table. Here is the select command.

SELECT 
	OBJECT_NAME(object_id) AS 'Table Name', 
	object_id, 
	name AS 'Column Name'
FROM 
	sys.columns 
WHERE 
	name LIKE '%ProductID%'

If you know any other method please do add them by commenting below.

Related Articles

• Get the row count of all the tables In a database.
• Search For Stored Procedure.
• Get the list of all the user defined functions in a database.

Reference

• Stackoverflow