What is a SQL Server Data Dictionary?

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

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 containing 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 occurrences 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 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 catalogue views. If you have a particular question about the structure of a database, these are the tables 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.