![]() Listed below are some of the more popular INFORMATION_SCHEMA views: This means that other database vendors which comply with the ISO standard will supply the same set of views. The INFORMATION_SCHEMA views included in SQL Server comply with the SQL-92 ISO standard. But once you know the data dictionary, you can write a simple query and get that information very quickly. 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. For example, how can you easily find all the tables and views using the column BusinessEntityID? 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. Though you can directly query these tables, Microsoft reserves the right to modify the system tables that make up the data dictionary.īecause of this, they recommend you query the INFORMATION_SCHEMA views as opposed to directly accessing the tables. INFORMATION SCHEMAĪ series of system tables stores related information know as the the data dictionary. It is how is “knows” about tables, columns, data types, indexes, foreign key constraints, and more. The object explorer used the data dictionary to display all it’s information. 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. In fact, this means you can get information about the DB through queries!ĭesigners and developers use the data dictionary to understand the structure of the database. This makes it a great resource to look up information. What I like is it contains information about tables and views. ![]() 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. Read More: Database Normalization – in Easy to Understand English > Other Uses for the Data Dictionary 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. ![]() The data dictionary becomes a guide book, per se, for SQL Server to use to access your data. 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. To do this quickly it references the data dictionary. 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. SQL Server uses the database dictionary to verify SQL statements. List All Table and View by Name indicating type.Find all tables containing the column BusinessEntityID.Find all tables with the characters ‘part’ in their name using the Data Dictionary.List the Number of Tables in Each Schema.Listing all tables that include a specified column.Examples using INFORMATION_SCHEMA views.How SQL Server uses the Data Dictionary.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |