What is a Data Dictionary?
The SQL Server data dictionary stores information about a database’s definition. The dictionary contains information about database objects such as tables, indexes, columns, datatypes, and views.
The SQL Server DBMS uses the data dictionary to execute queries. The data dictionary is kept up-to-date as the database objects are changed.
All the examples for this article are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can start learning SQL server today using my free guide Getting Started Using SQL Server.
Table of contents
How SQL Server uses the Data Dictionary
SQL Server uses the database dictionary to verify SQL statements. When you execute a SQL statement the DBMS (Database Management System) parses the statement and then determines whether the tables and fields you are referencing are valid. To do this quickly it references the data dictionary.
In addition to testing the validity of statements, SQL Server uses the data dictionary to assist with query plan generation and to reference information defining the structure of the database.
The data dictionary becomes a guide book, per se, for SQL Server to use to access your data. Simply put, without the data dictionary, though SQL Server would know and understand the SQL language, it wouldn’t know about your database tables and columns; therefore, it wouldn’t be able to query them.
Read More: Database Normalization – in Easy to Understand English >>
Other Uses for the Data Dictionary
Since the data dictionary contains the definition of the database, it is a really good resource for you to use to gain information about the database.
What I like is it contains information about tables and views. This makes it a great resource to look up information. In fact, this means you can get information about the DB through queries!
Designers and developers use the data dictionary to understand the structure of the database. You can think of the dictionary as an up-to-date reference document.
Design tools such as SQL Server Management Studio display information about the databases through the object explorer using the data dictionary.
The tables listed above aren’t magically known, rather, the object explorer issued a query to the data dictionary to retrieve all the user tables.
The object explorer used the data dictionary to display all it’s information. It is how is “knows” about tables, columns, data types, indexes, foreign key constraints, and more.
INFORMATION SCHEMA
A series of system tables stores related information know as the the data dictionary. Though you can directly query these tables, Microsoft reserves the right to modify the system tables that make up the data dictionary.
Because of this, they recommend you query the INFORMATION_SCHEMA views as opposed to directly accessing the tables.
Since you can query the data dictionary yourself, you can answer some questions that would otherwise require a lot of hunting and pecking through object explorer. For example, how can you easily find all the tables and views using the column BusinessEntityID?
Without the data dictionary, you’ll have to use object explorer and open each and every table and view and look through the definitions for the column. But once you know the data dictionary, you can write a simple query and get that information very quickly.
INFORMATION_SCHEMA Views
The INFORMATION_SCHEMA views included in SQL Server comply with the SQL-92 ISO standard. This means that other database vendors which comply with the ISO standard will supply the same set of views.
Listed below are some of the more popular INFORMATION_SCHEMA views:
- COLUMNS – Return one row for each column the current user has access to use in the current database.
- TABLES – Return one row for each table the users have access to use within the current database. You’ll find information for both table and view here.
- VIEW_TABLE_USAGE – Query this view to know what tables are used to define a view.
- VIEWS – List all views that you are able to access with your current permissions.
Examples using INFORMATION_SCHEMA views
Listing all tables that include a specified column
You can do this by using INFORMATION_SCHEMA.COLUMNS view. For instance the following lists all tables and views contain the column BusinessEntityID
SELECT TABLE_NAME FROM AdventureWorks2012_Data.INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'BusinessEntityID'
As you can imagine the data dictionary can come in handy! Can you imagine having to search every definition by hand to find all the occurrence of a field within the database?
List all tables in a database
Use the INFORMATION_SCHEMA.TABLES view to do this.
SELECT TABLE_NAME, TABLE_TYPE FROM AdventureWorks2012_Data.INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME
This query returns both base tables as well as views.
List the Number of Tables in Each Schema
This example lists the number of tables and views defined within a schema.
SELECT TABLE_SCHEMA, Count(TABLE_SCHEMA) FROM AdventureWorks2012_Data.INFORMATION_SCHEMA.TABLES GROUP BY TABLE_SCHEMA
SQL Server Catalog Views
The catalog views are another way to view the data dictionary. I feel the object catalog views provide more information and I tend to use these views over the INFORMATION_SCHEMA views.
Some of the views I’ve found most useful include:
- objects – contain a row for each object, such as a FOREIGN KEY or PRIMARY KEY constraint defined within the database.
- columns – contain a row for each column of an object such as view or tables.
- tables – return a row for each table object
Examples using Catalog Views
The following examples use the Microsoft SQL Server catalog views to obtain the same information use did so above using the INFORMATION_SCHEMA.
Listing all tables that include a specified column
Both the sys.tables and sys.columns view are together to get the names of the tables using a specific column.
To do so, join the object_id. This ID is used to identify common database objects, such as tables and views.
SELECT t.name,
t.type_desc
FROM AdventureWorks2012_Data.sys.columns AS c
INNER JOIN sys.tables AS t
ON c.object_id = t.object_id
WHERE c.name = 'BusinessEntityID'
List all tables in a database
Use the sys.tables view to list all table defined within the database. This view returns both base tables and views.
SELECT name,
type_desc
FROM AdventureWorks2012_Data.sys.tables
ORDER BY Name
List Number of Tables in Each Schema
The sys.tables view doesn’t include a schema name, so the built-in function SCHEMA_NAME is used to obtain it.
SELECT SCHEMA_NAME(schema_id),
count(name)
FROM AdventureWorks2012_Data.sys.tables
GROUP BY SCHEMA_NAME(schema_id)
As you can see from the example, it a little more technical than using the INFORMATION_SCHEMA. However, I say from experience that there is much more information contained in the catalog views… If you have a particular question about the structure of a database, these are the table to hit.
Now that you know about the data dictionary what ideas do you have to utilize this great resource? Leave a comment and share with us your ideas!
Data Dictionary 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?
To answer this 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, let’s get to solving the puzzle!
Find all tables with the characters ‘part’ in their name using the Data Dictionary
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.
The 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 Table and View by Name indicating type.
Earlier we used the SYS.tables list all user defined tables. This table is a subset of the objects conains 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 give you a glimpse into type of problems you can solve using the data dictionary. As you become more advanced, you may even look to use the data dictionary to assist in dynamic query generation!
Leave a Reply