Have you ever wondered how to easily access details about databases and their objects in SQL Server? Look no further than the INFORMATION_SCHEMA database. In this article, we will explore what the INFORMATION_SCHEMA database is, how it can be used, and the benefits it provides.

What is the INFORMATION_SCHEMA Database?

The INFORMATION_SCHEMA database is a set of views that can be found in SQL Server, as well as other database systems like MySQL. It provides read-only access to information about databases and their objects, such as tables, constraints, procedures, and views, stored on the server. This database can be incredibly useful for tasks such as checking the server and database status, comparing changes, and automating processes.

Listing All Databases

One of the first things you might want to do is list all the databases on your server. There are a few ways to accomplish this, but let’s focus on two SQL Server-specific methods:

SELECT * FROM sys.databases;
EXEC sp_databases;

The first query uses the sys.databases object to retrieve detailed information about each database. The second query executes the system stored procedure sp_databases, which returns a predefined set of columns. Both methods provide valuable information, but the first query offers more details.

Using INFORMATION_SCHEMA to Access Tables Data

Now that we understand the basics of the INFORMATION_SCHEMA database, let’s explore how we can use it to access table data. The following queries demonstrate how to list all tables and constraints in a selected database:

USE your_database_name;

-- List all tables in the selected database
SELECT * FROM INFORMATION_SCHEMA.TABLES;

-- List all constraints in the selected database
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS;

Before executing these queries, make sure to specify the database you want to work with using the USE statement. The first query retrieves a list of all tables in the database, while the second query returns information about the constraints defined for those tables. These queries can provide valuable insights into the structure of your database.

Additionally, you can join the INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.TABLE_CONSTRAINTS tables to create custom queries. For example, the following query counts the number of primary keys, unique constraints, and foreign keys in each table:

USE your_database_name;

-- Join tables and constraints data
SELECT INFORMATION_SCHEMA.TABLES.TABLE_NAME,
       SUM(CASE WHEN INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'PRIMARY KEY' THEN 1 ELSE 0 END) AS pk,
       SUM(CASE WHEN INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'UNIQUE' THEN 1 ELSE 0 END) AS uni,
       SUM(CASE WHEN INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY' THEN 1 ELSE 0 END) AS fk
FROM INFORMATION_SCHEMA.TABLES
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON INFORMATION_SCHEMA.TABLES.TABLE_NAME = INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
GROUP BY INFORMATION_SCHEMA.TABLES.TABLE_NAME
ORDER BY INFORMATION_SCHEMA.TABLES.TABLE_NAME ASC;

This query allows you to easily identify tables without primary keys or foreign keys, which can be indicative of design errors. It also helps you identify tables with multiple unique constraints, which may require further investigation.

The INFORMATION_SCHEMA Tables

The INFORMATION_SCHEMA database contains various tables (views) that provide detailed information about different aspects of your database. Here is a list of some of the tables available:

• CHECK_CONSTRAINTS – details related to each CHECK constraint
• COLUMN_DOMAIN_USAGE – details related to columns with alias data types
• COLUMN_PRIVILEGES – columns privileges granted to or granted by the current user
• COLUMNS – columns from the current database
• CONSTRAINT_COLUMN_USAGE – details about column-related constraints
• CONSTRAINT_TABLE_USAGE – details about table-related constraints
• DOMAIN_CONSTRAINTS – details related to alias data types and rules
• DOMAINS – alias data type details
• KEY_COLUMN_USAGE – details about keys and their columns
• PARAMETERS – details related to parameters of user-defined functions and procedures
• REFERENTIAL_CONSTRAINTS – details about foreign keys
• ROUTINES – details related to routines (functions & procedures)
• SCHEMATA – details related to schemas
• TABLE_CONSTRAINTS – details related to table constraints
• TABLE_PRIVILEGES – table privileges granted to or granted by the current user
• TABLES – details related to tables
• VIEW_COLUMN_USAGE – details about columns used in views
• VIEW_TABLE_USAGE – details about tables used in views
• VIEWS – details related to views

These tables provide a wealth of information that can be used for documentation, analysis, and automation purposes.

Conclusion

The INFORMATION_SCHEMA database in SQL Server is a powerful tool for accessing information about databases and their objects. By querying the views provided by this database, you can gain insights into the structure of your database, automate processes, and ensure data integrity. Take some time to explore the various tables in the INFORMATION_SCHEMA database and discover the possibilities it offers.