When working in a SQL Server or Azure SQL database containing many tables, I often find the need to search for tables with names that contain a specific keyword. This is particularly true when working with an unfamiliar schema.
While tools like Azure Data Studio provide easy ways to search by table name, I find it helpful to have a SQL query to hand which I can customize to return additional metadata regarding tables while searching for them.
In this article, I will provide a simple SQL query that you can use to search for tables by their name. I’ll also take a quick look at some alternative approaches you can use to obtain the information you need.
The Query
Without further ado, here is the query that you can run against either a SQL Server or Azure SQL database to search for table names that contain a specific keyword (or keywords).
-- Update the @TableName variable with the column search term. DECLARE @TableName AS VARCHAR(MAX) SET @TableName = '%TABLE_TO_FIND%' SELECT sys.schemas.[name] AS [Schema], sys.tables.[name] AS [Table], sys.tables.create_date AS [Created Date], sys.tables.modify_date AS [Modified Date], sys.tables.max_column_id_used AS [Max Column ID Used] FROM sys.schemas INNER JOIN sys.tables ON sys.schemas.schema_id = sys.tables.schema_id WHERE sys.tables.[name] LIKE @TableName ORDER BY sys.tables.[name]
Before executing the query, update the @TableName variable with your search term e.g. you could change the '%TABLE_TO_FIND%' text to '%product%' to search for all tables that contain the word ‘product’.
The Results
If you run the query against the AdventureWorks database when searching for the text '%product%', the output should look similar to the screenshot below.
The results are ordered alphabetically by the table name and contain the key details that you will usually need to know when searching for tables, including both the Schema and the name of the Table.
The ‘Created Date’ and ‘Modified Date’ columns help to identify how old a table is and when its schema was last amended.
The ‘Max Column ID Used’ column is useful for identifying roughly how many rows the table is likely to contain and will allow you to see how close the ID may be to the maximum SQL Server integer value if your IDs are based on the INT column type.
Concerning the SQL query, there are other ways of getting similar information. For example, you can select from the INFORMATION_SCHEMA.TABLES system table and your query will work across any database which adheres to the ANSI Standard.
However, I find it useful when working with SQL Server databases to include some of the extra, non-standard information the above query can provide.
Alternatives
Before wrapping things up, I would like to briefly explore a couple of alternative approaches that you could use to search for database tables by their name.
Object Explorer Details
The Object Explorer Details Pane is what I would consider to be one of the less commonly used features of SQL Server Management Studio (SSMS) but it is in fact pretty useful.
You can access the Object Explorer Details Pane within SSMS via the menu bar by going to View –> Object Explorer Details or by using the F7 keyboard shortcut.
Once the Pane is open you can navigate to ‘Databases’, select the database you are interested in and then navigate to ‘Tables’ to view a list of all the database tables. Once you are in the Tables view, you can use the ‘Search’ box to search for tables with a specific name, as shown in the screenshot below.
The Object Explorer Details approach is convenient since it is a feature that is built directly into SSMS and allows you to search for objects across multiple databases if required. However, it is somewhat limited in its filtering capabilities and can only display the Name, Type, Schema and PowerShell Path to the object as shown in the screenshot.
Azure Data Studio
Much like the way the SSMS is based on Visual Studio, Azure Data Studio is based on Visual Studio Code. Broadly speaking, both Azure Data Studio and Visual Studio Code are considered the “cool kids on the block” these days.
If you’ve used Azure Data Studio before, it’s more than likely that you have already availed of the built-in search functionality that it provides, since it is readily available within the main user interface.
If Azure Data Studio is new to you then I would encourage you to check it out. It’s open-source and cross-platform, so you can run it on Linux, Mac, and Windows machines.
The screenshot below shows how you can search for tables by their name within Azure Data Studio.
Similar to the Object Explorer Details approach, the functionality provided by Azure Data Studio is convenient to access and use but is limited to displaying the Name, Schema, and Type of the object within the list view.
Summary
In this post, I provided a simple SQL query that allows you to search for database tables by name and looked at some alternative ways of getting similar information using the SSMS and Azure Data Studio tools.
Please feel free to use and amend the SQL script I have provided as you see fit. There is scope to select additional information that is joined from other system tables to suit your particular requirements.
You can also check out my SQL Server Scripts GitHub repository and SQL Archives for other helpful SQL scripts.
Comments