In this puzzle, we’re going to learn how to query the data dictionary using SQL Server. Knowing how to query the data dictionary is good to know. There are many questions you can answer about your database through the data dictionary.
For instance, do you know how many of your table don’t have a primary key defined?
By reading this article you’ll learn to query the data dictionary, but before you read the entire article, try the puzzle. Any work you do, if it you just get part of the answer, helps to reinforce the concepts you’ll learn.
Solving puzzles is a great way to learn SQL. Nothing beats practicing what you’ve learned. Once you have figured out the puzzle, post your answer in the comments so we all can learn from one another. We also discuss puzzle and more in Essential SQL Learning Group on Facebook. Be sure to find us there!
SQL Puzzle Question
You’re getting ready for a new crop of summer interns. Last year they all got lost in the database, and you constantly had to save them.
You’ve learned from your mistakes! Now you’re going to make sure they can find their way around by using the built-in data dictionary.
So, you’ve decided to give them a lesson. You want them to answer the following questions:
- Find all tables with the characters ‘part’ in their name
- Find all tables containing the column BusinessEntityID
- List all tables all tables and views alphabetical order by name.
Since you need to know your stuff, what are the answers? Can you provide the SQL you would use?
Introduction to the Data Dictionary
If you’re not familiar with the concept of a data dictionary, then I would recommend you first read my introductory article. It gives you a high-level overview of it purpose, and some examples to get you started.
To answer today’s puzzle, we are going to use three tables from the data dictionary:
- tables – Lists all tables defined in the database; object_id is the primary key.
- columns – Lists all columns for all tables. It is related to SYS.tables by object_id
- objects – Contains all database objects, such as tables and views.
Also, there a many tables in the data dictionary that aren’t covered in this article, we’re only scratching the surface, so I would recommend you check out the SQL Server System Views poster to learn more. You can download it from Microsoft.
Now, here are the answers to this week’s puzzle.
Find all tables with the characters ‘part’ in their name
To find the name of all tables containing the letters ‘part’ in their name we’ll query SYS.tables. This table contains a row for each user table within the database.
To find all tables containing ‘part’ we will use the LIKE statement along with the % wildcard characters to get a partial match. Here is the query.
SELECT name FROM SYS.tables WHERE name LIKE '%part%'
Which produces these results:
Find all tables containing the column BusinessEntityID
In order to find all the tables containing a column named BusinessEntityID, we’ll work with two tables from within the data dictionary.
This first is SYS.tables, which we already know contains a row for every user table. The second is SYS.columns, which contains a row for every column defined within tables and views.