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