Learn to Use the Data Dictionary in SQL Server

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 BusinessEntityID
• 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 SYS.tables by object_id.
• 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.

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.

SYS.columns is related to SYS.tables by object_id. There can be many SYS.columns rows for each SYS.tables row.

Given this relationship, your first reaction may be to write a query to join tables to columns like so:

SELECT   t.name
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 BusniessEntityID and 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.

SELECT   t.name
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 ‘BusinessEntityID.’

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.obects. 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 ‘V’.

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 ‘V’.

Here is the query you can use to do this:

SELECT Name,
       CASE
          WHEN Type = 'U' THEN 'Table'
          WHEN Type = 'V' THEN 'View'
          ELSE 'Unknown'
       END as Type
FROM   SYS.objects
WHERE  Type IN ('U','V')
ORDER BY Name

Here are the results:

Conclusion

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!