How to Create a Simple Find Feature for your SQL Server Database
How to Create a Simple Find Feature for your SQL Server Database
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
You May Also Like
A Simple Introduction to Cisco CML2
0 3703 0Mark Jacob, Cisco Instructor, presents an introduction to Cisco Modeling Labs 2.0 or CML2.0, an upgrade to Cisco’s VIRL Personal Edition. Mark demonstrates Terminal Emulator access to console, as well as console access from within the CML2.0 product. Hello, I’m Mark Jacob, a Cisco Instructor and Network Instructor at Interface Technical Training. I’ve been using … Continue reading A Simple Introduction to Cisco CML2
How to Build in a PSMethod to your PowerShell Code
0 68 0In this video, PowerShell instructor Jason Yoder shows how to add Methods (PSMethod) to your code using free software that’s added into the PSObject. For instructor-led PowerShell courses, see our course schedule. Microsoft Windows PowerShell Training Download the Building Methods PowerShell script</a> used in this video. <# ╔══════════════════════════════════════════════════════════════════════════════╗ ║ ║ ║ Building Methods ║ ╟──────────────────────────────────────────────────────────────────────────────╢ … Continue reading How to Build in a PSMethod to your PowerShell Code
OSPF Adjacency Troubleshooting Solution – Getting Close to the OSPF adj
0 247 1In this video, Cisco CCNA & CCNP instructor Mark Jacob shows how to troubleshoot OSPF Adjacency issues by showing the distance between routers with the show ip ospf neighbor command.