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 tables 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 you just get part of the answer, it 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 can all learn from one another.
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
- List all tables and views in 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 its 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
objects – Contains all database objects, such as tables and views.
Also, there are 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.
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.
SYS.columns is related to
object_id. There can be many
SYS.columns rows for each
Given this relationship, your first reaction may be to write a query to join tables to columns like so:
FROM sys.tables t
INNER JOIN SYS.columns c
ON t.object_id = c.object_id
WHERE c.name = 'BusinessEntityID'
ORDER BY t.name
However, given there are many columns in a table, this query potentially could return duplicate table names. For instance, if our table contained columns names
OldBusinessEntityID, this would happen.
To get around this, you can use a correlated subquery with an
EXISTS operator. The idea is to see if at least one column matches the criteria, if so, then include the table name in the result.
FROM sys.tables t
WHERE EXISTS (SELECT 1
FROM SYS.columns c
WHERE t.object_id = c.object_id
AND c.name = 'BusinessEntityID'
ORDER BY t.name
Here are the tables that have at least one column whose name contains ‘
List All Tables and Views by Name Indicating Type
Earlier, we used the
SYS.tables list in all user defined tables. This table is a subset of the objects contains in
SYS.objects contains many entries including user defined tables and views.
SYS.objects.type indicated the type of object represented in the row. User defined tables have the value ‘
U’ and views ‘
In order to list all user defined tables and view, all we need to do is query
SYS.objects and limit our results to TYPE’s ‘
U’ and ‘
Here is the query you can use to do this:
WHEN Type = 'U' THEN 'Table'
WHEN Type = 'V' THEN 'View'
END as Type
WHERE Type IN ('U','V')
ORDER BY Name
Here are the results:
This puzzle just gives you a glimpse into the type of problems you can solve using the data dictionary. As you become more advanced, you may even look at using the data dictionary to assist in dynamic query generation!