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. Because of this, they recommend you query the INFORMATION_SCHEMA views as opposed to directly accessing the tables. Though you can directly query these tables, Microsoft reserves the right to modify the system tables that make up the data dictionary.
The data dictionary is stored in a series of system tables.
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. SQL Server Management Studio Object Explorer You can think of the dictionary as an up-to-date reference document.ĭesign tools such as SQL Server Management Studio display information about the databases through the object explorer using 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!ĭata dictionaries are used by designers and developers to understand 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. 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. 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.
You can get started using these free tools using my& Guide& Getting Started Using SQL Server. 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.Īll the examples for this article are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. 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.