Click here to Skip to main content
14,266,113 members

What is a SQL Server Data Dictionary?

Rate this:
5.00 (1 vote)
Please Sign up or sign in to vote.
5.00 (1 vote)
14 Aug 2019CPOL
What is a Data Dictionary? In SQL Server the data dictionary is a set of database tables used to store information about a database’s definition.  The dictionary contains information about database objects such as tables, indexes, columns, datatypes, and views.

What is a Data Dictionary?

In SQL Server the data dictionary is a set of database tables used to store information about a database’s definition.  The dictionary contains information about database objects such as tables, indexes, columns, datatypes, and views.

The data dictionary is used by SQL server to execute queries and is automatically updated whenever objects are added, removed, or changed within the database.

All the examples for this article are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database.  You can get started using these free tools using my Guide Getting Started Using SQL Server.

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 databases tables and columns; therefore, it wouldn’t be able to query them.

Other Uses for the Data Dictionary

Since the data dictionary contains the definition of the database, it is really good resource for you to use to gain information about the database.  The really cool thing is that the data dictionary is made up of SQL tables and views.  This means, you can get information about the DB through queries!

Data dictionaries are used by designers and developers 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.

SSMS Object Explorer

SQL Server Management Studio Object Explorer

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 data dictionary is stored in a series of system tables.  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.  However, using the data dictionary this can be done using a simple query.

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.

Here are some of the more commonly used views and their descriptions:

  • COLUMNS – Return one row for each column the current user has access to use in the current database. This view can be used to determine the data type and table the column is defined for use.
  • TABLES – Return one row for each table the users has access to use within the current database. Note, both tables and views are returned using the TABLES view.
  • VIEW_TABLE_USAGE – Return one row for each table that is used in a view within the current database.
  • VIEWS – Return one row for views that can be accessed using the currents user’s permissions from the current database.

Examples using INFORMATION_SCHEMA views

Listing all tables that include a specified column

You can do this by using the 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

In this example each schema and the number of tables and views defined within them are listed:

SELECT   TABLE_SCHEMA, Count(TABLE_SCHEMA)
FROM     AdventureWorks2012_Data.INFORMATION_SCHEMA.TABLES
GROUP BY TABLE_SCHEMA

SQL Server Catalog Views

The catalogue views are another way to view the data dictionary.  If feel the object catalogue views provide more information and I tend to use these view over the INFORMATION_SCHEMA views.

Some of the views I’ve found most useful include:

  • objects – contains 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

In order to get the names of the tables using a specific column both the sys.tables and sys.columns view must be used together.  They are joined via the object_id, which 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

The sys.tables view can be used to get names of all tables defined in 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 form 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 catalogue 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!

The post What is a SQL Server Data Dictionary? appeared first on Essential SQL.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

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/

Comments and Discussions

 
PraiseThanks Kris Pin
apembo14-Aug-19 22:28
memberapembo14-Aug-19 22:28 

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.

Technical Blog
Posted 14 Aug 2019

Tagged as

Stats

1K views