How to search for column names in SQL Server

Introduction

This article explains the easiest way to search for a column name in SQL Server. This sounds like a simple task on its own and it is, but there are several solutions for this task and the purpose of this write-up is to show the most efficient way of completing it.

Two methods will be shown as a solution. One is the native way by simply querying the system information schema views. This schema is held within each database and it contains metadata for all database objects stored in the database. Furthermore, the third-party software solution called ApexSQL Search will be shown that can be used in the same way as any search engine is used on the internet to find results.

Scenario

The challenge could be as simple as that there’s a need to search for column names in SQL Server to find duplicate names or even to search if a column exists, especially on large databases with thousands of tables. There are many solutions on the internet for this and most of the suggestions are different scripts joining sys.table with sys.columns that require the user to change the parts here and there to meet requirements.

Native solution

Let’s launch SQL Server Management Studio and see what scripts can be used for searching databases and finding a specific column name. The AdventureWorks2014 sample database is used throughout all examples.

The query below finds partial matches because it has Like and % wildcard characters in the Where clause:

SELECT Table_Name, 
    Column_Name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'AdventureWorks2014'
   AND COLUMN_NAME LIKE '%address%';

Column names are variables that need to be specified to meet the search criteria:

• TABLE_CATALOG – AKA Table qualifier is where the targeted database should be specified under single quotation marks
• COLUMN_NAME – This is where the search for column name in SQL Server should be specified, also under single quotation marks

In this particular case, the targeted database is set to AdventureWorks2014, and the column name is telling the SQL Server to look for all possible beginnings and endings to that “address” root.

This simple query returns all column names that have string “address” in their names:

The search term that is specified is not case sensitive. As can be seen above, the results for the search phrase “address” will also return “Address” records.

If the condition is to find exact matches, lose both the Like operator and the percent signs and use the equal operator in the Where clause like shown below:

SELECT Table_Name, 
    Column_Name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'AdventureWorks2014'
   AND COLUMN_NAME = 'addressline1';

This narrows down the search for column name in SQL Server to only find the exact matches like shown below:

It goes without saying, but losing the Where clause entirely will return a list of all the columns and tables in the database:

SELECT Table_Name, 
    Column_Name
FROM INFORMATION_SCHEMA.COLUMNS

It cannot be seen in the screenshot below, but the query returned 744 rows in total:

With these few simple scripts, a user is fairly equipped to look up the tables in a database that contains a particular column name. Furthermore, if users want to find all the column names from the database run these scripts and down any condition in Where clause to get the desired result.

Third-party software solution

Let’s see how to search for a column name in SQL Server using a lightweight add-in for SSMS and achieve the same result from above.

Installation

First things first, the SQL search add-in should be downloaded from this link. Run the executable installer and click the Next button on the welcome screen.

Next, make sure to check “I accept the terms in the license agreement”, and click the Next button to proceed with the installation:

In the next step, feel free to change the installation path to a different location, but if the default is okay, just hit the Next button again and move to the next step:

In the final step, it important to select at least one host for integration for the SQL search add-in to integrate to. By default, all supported SSMS and VS environments for managing any SQL infrastructure will be listed and checked.

In this particular example, Microsoft SQL Server Management Studio 18 and Visual Studio 2019 are installed on a machine on which the installer is run and by clicking Install, SQL search add-in will be integrated into both hosts:

Once ApexSQL Search add-in is successfully installed, click the Close button to exit the installation wizard.

Column search

Now, let’s see how to search for a column name in SQL Server using this lightweight add-in. A feature that can be used to search for column names in SQL Server is Object search. This feature allows users to find all SQL objects containing the specified phrase.

Start either SSMS or VS and connect to a SQL Server instance. From the main menu in SSMS, navigate to ApexSQL > ApexSQL Search > Object search as shown below:

This will open the Object search panel within SSMS for searching all SQL Server objects by entering a search phrase and clicking the Find button or hitting Enter from the keyboard.

Before searching for a column name in SQL Server, verify that the correct SQL Server is selected from the drop-down list, as well as the targeted database as highlighted below:

To narrow down the search scope, select the object types that need to be searched from the Object types drop-down menu. To achieve results from the beginning of the article, unselect all objects by clicking the Select all option in the upper left corner. Then select only Columns from the list and click the OK button to collapse the menu and save changes:

This action will ensure that only columns will be included in the search.

To perform a search for the column name in SQL Server, simply enter the search term in the Search text box. Same as the script used at the beginning, “%address%” (SQL search add-in supports wildcards as the Like operator in SQL Server) is specified without single quotation marks, of course, and filters can be left as it is:

The result is the same number (31) that was also returned by the first query that finds partial matches.

Moving on, let’s see how to search column names in SQL Server and find the exact matches like the condition to find exact matches in the script before.

In the Search text box, type the same search term as before addressline1, and on the right, check the Exact match option. Click the Find button or hit the Enter key (works only if the cursor is in the search box) from the keyboard:

Just like that, the search for column name in SQL has been narrowed down only to find the exact matches like shown below:

As can be seen, this search returned 6 results, the same number as the query did before.

What’s neat about this SQL search is that various information is displayed for the corresponding columns like schema and database they belong to, where the search term was found, etc.

Furthermore, unlike the Results grid that only displays search results, there is a right-click context menu that offers many options like Navigate to object explorer node that quicky finds a location in Object Explorer panel of the founded columns or any other object:

Last but not least, let’s also see how to search for all column names in SQL Server using this add-in.

This is pretty simple, just make sure that at least one checkbox is selected to define the search, but leave the Search text box empty, and click either the Find button or hit Enter:

Notice the number in the status bar that indicates 740 matches were found in the search for column names in SQL Server. That’s all the columns in the targeted database. But wait, why does this number not match the result (744) of a query? The previous two examples were an exact match.

Well, the answer to this question lies within what is being queried under the hood in the SQL search add-in, the complexity of the query, inner joins used, conditions in the Where clause, etc.

The add-in also queries sys.columns and the difference between that one and INFORMATION_SCHEMA.COLUMNS used in scripts is the object types they cover. In other words, it’s very unlikely for these two actions to use the same methods and that’s why there’s a little gap in final results.

Conclusion

This was a simple approach on how to search for a column name in SQL Server. Two methods were shown for finding all the column names from a sample database by running a few simple scripts and a way of achieving the same results using a third-party add-in for SSMS and VS. Having useful scripts at hand is always a good idea, but why not replace them with a lightweight SQL search add-in that does the same job in just a few clicks.