How to Create a Simple Find Feature for your SQL Server Database

Home > Blogs > SQL Server > How to Create a Simple Find Feature for your SQL Server Database

How to Create a Simple Find Feature for your SQL Server Database

Like This Blog 0 Peter Avila
Added by March 21, 2013

As we see in the SQL100:Introduction to Transact-SQL and SQL250:Transact-SQL for Developers courses, SQL Server Management Studio (SSMS) has a view (aka panel or window) called Object Explorer that contains all the metadata of a database and much more. But when my students ask me if it has a simple find feature that allows them to, let’s say, find all tables containing a certain column, I have to tell them that it does not. The good news is that we can create a simple Find feature very easily.

In this post, I’ll use the AdventureWorks database to create an in-line table-valued function that will accept a parameter, in this case the column name, and will return a list of the names of all tables that contain that column. Then I’ll point out how you can use a similar approach to find views, stored procedures and other database objects in addition to tables.

I will use two system views. System views come with SQL Server and can be used in any database. System views provide system information, including metadata, performance, and other system information (more on system views at the end of this post).

One of the system views is sys.Tables. It lists all the tables in the database along with plenty of useful information about each table. Notice, in particular, that sys.Tables contains the names of the tables and an object_id. The object_id is a unique identifier in this view (no two tables listed in this view have the same object_id).

The second system view I’ll need is sys.Columns. This view lists all the columns in all database objects that have columns, such as tables, views, and others. Notice that the names of the columns are listed along with an object_id. The object_id in this view references the object_id in the sys.Tables view (and in other views as I’ll discuss below).

By using a simple inner join on the object_id, we can associate each column object with its table:

This is a useful query, but we don’t want to have to re-create this join each time we need it. Let’s put it into a function so we can call it whenever we need it.

The function will return a list of the names of all the tables that contain a certain column. Because the function will return data in tabular form (the list of names), we will create a table-valued function. And because the table returned will be defined by a single Select statement (our join query), we’ll create an in-line table-valued function. Let’s call it FindTablesWith and put it in the dbo schema for now. It will accept one parameter, we’ll call it @ColumnName.

We’ll make two small changes to our join query. We’ll need to add a Where clause to filter the results by the parameter value (the column name), and we’ll stop displaying the column name, which is now an input. Here’s the statement that creates the FindTablesWith function:

After we execute the Create Function statement, we can see if the statement did in fact create the function in the database by expanding the database’s Programmability folder, then the Functions folder, and finally the Table-valued Functions folder. If you don’t see the function listed, right-click on the Functions folder and select Refresh then try selecting it again.

So next time we need to find the names of all tables that contain a certain column, we can just query the FindTablesWith function:

You can use this same approach to find other types of objects in SQL Server by using different system views. Check out sys.views that lists all views, sys.procedures that lists all stored procedures, and others. All of these and more are listed under the System Views folder in your database:


Peter Avila
SQL Server Instructor – Interface Technical Training
Phoenix, AZ

Videos You May Like

Agile Methodology in Project Management

0 153 0

In this video, you will gain an understanding of Agile and Scrum Master Certification terminologies and concepts to help you make better decisions in your Project Management capabilities. Whether you’re a developer looking to obtain an Agile or Scrum Master Certification, or you’re a Project Manager/Product Owner who is attempting to get your product or … Continue reading Agile Methodology in Project Management

JavaScript for C# Developers – September 24, 2014

0 487 3

Is JavaScript worth taking the time to learn if I’m a server-side .NET developer? How much of C# carries over to JavaScript? In this recorded video from Dan Wahlin’s webinar on September 24,2014, Dan answers these questions and more while also discussing similarities between the languages, key differences, and the future of JavaScript (ES6). If … Continue reading JavaScript for C# Developers – September 24, 2014

Detailed Forensic Investigation of Malware Infections – April 21, 2015

4 602 5

How does an investigator hunt down and identify unknown malware? In this recording of our IT Security training webinar on April 21, 2015, Security expert Mike Danseglio (CISSP / CEH) performed several malware investigations on infected computers and identify symptoms, find root cause, and follow the leads to determine what’s happening. He demonstrated his preferred … Continue reading Detailed Forensic Investigation of Malware Infections – April 21, 2015

Write a Comment

Share your thoughts...

Please fill out the comment form below to post a reply.