Like This Blog 0

Added by Peter Avila 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:

Enjoy!

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