Click here to Skip to main content
13,404,806 members (43,372 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

3.4K views
5 bookmarked
Posted 30 Dec 2017
MIT

Learn to Use the Data Dictionary in SQL Server

, 30 Dec 2017
Rate this:
Please Sign up or sign in to vote.
In this puzzle, we’re going to learn how to query the data dictionary using 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:

Data Dictionary Answer 1

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.

Data Dictionary Relationships

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

Data Dictionary Answer 2

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:

Data Dictionary Answer 3

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!

License

This article, along with any associated source code and files, is licensed under The MIT License

Share

About the Author

essentialSQL
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here ==> http://www.essentialsql.com/get-started-with-sql-server/

You may also be interested in...

Pro
Pro

Comments and Discussions

 
Question[My vote of 2] Deprected Pin
pt140113-Jan-18 4:22
memberpt140113-Jan-18 4:22 
QuestionAbout duplicate table names... Pin
Member 96861841-Jan-18 10:36
memberMember 96861841-Jan-18 10:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.180221.1 | Last Updated 30 Dec 2017
Article Copyright 2017 by essentialSQL
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid