In this post, we want to share 6 Useful SQL Server Data Dictionary Queries Every DBA Should Have. Before we get started, if you face problem to activate Microsoft Visual Studio for Product Key, please go through the following article: Visual Studio Product Key Collection.

 

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, data types, and views. The data dictionary queries are used by SQL Server to execute and are automatically updated whenever objects are added, removed, or changed within the database.

 

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.

 

Top 6 Useful SQL Server Data Dictionary Queries

Let’s see This is a list of SQL queries for SQL Server Catalog Views / Data Dictionary handy for any SQL Server DBA.

1. List of tables with a number of rows and comments

This query returns the list of tables in a database sorted by schema and table name with comments and the number of rows in each table.

 

2. List of views with definition and comments

This query returns the list of database views with their definition SQL and a comment.

 

3. Table columns details

This query returns list of tables and their columns with details.

 

4. Foreign keys

This query returns the list of tables and their foreign keys.

 

5. Views columns

This query returns the list of views with their columns.

 

6. Tables by number of columns

This query returns the list of tables sorted by the number of columns they contain.

 

Leave a Comment