SQL Server INFORMATION_SCHEMA Views

Introduction

Have you ever come across a situation where you need to check first if a table exists? Or have you encountered a scenario where you need to check if the table's column exists? Then alter the table by adding your desired column. If you have answered yes to any of these two, you have come to the right place.

This post will explore the INFORMATION_Schema views, learn what it is, and show you some common usages.

By the way, I'll be using SQL Server 2016, which can also be applied to other older and later versions of SQL Server.

What is INFORMATION_SCHEMA Database?

The INFORMATION_SCHEMA stores other databases' details on the server.

It allows you to retrieve views/information about the tables, views, columns, and procedures within a database.

The views are stored in the master database under Views->System Views and will be called from any database you choose.

Let's see a screenshot below,

Benefits

As a database developer or administrator, understanding schema and what tables are in a specific database gives us a good idea of the underlying structure, which can help us write SQL queries. It also allows us to check if everything is expected or on the database. Moreover, it helps us avoid running queries multiple times to see if the schema or column names are correct.

Common Usages

Before we start, we'll use the Northwind database for all our examples. You can download it from here. Now, here are the steps we're going to take. First, get all the databases on our local SQL Server instance, get all the tables; third, let's search for a column; fourth, search for constraints; and last, query some views.

Showing All Databases

Let's first try to show all the databases on our current server instance.

Note that my result will differ from yours, but you'll see all the databases on your SQL Server instance once you run the query below.

SELECT * FROM sys.databases;

EXEC sp_databases;

Output

The syntax on how we could show the database isn't directly related to INFORMATION_Schema.

However, it is an excellent start as we go through databases, tables, columns, and keys.

Showing All Tables

Now that we know how to get the database on a SQL Server instance.

In this section, we will try to get all the possible tables of the Northwind database.

USE [Northwind];
--show all table
SELECT * FROM INFORMATION_SCHEMA.Tables;

Querying Column of a Specific Table

In this example, we'll explore how to check a table and a column if it exists.

Then add a new column to the categories table. Then let's set Tags as its column name and its data type as NVARCHAR(MAX).

Let's try to see a sample query below.

USE [Northwind];

/* 
 * Let's try to declare some variables here that we can use later when searching for them. 
 */
DECLARE @TABLE_NAME NVARCHAR(50);
SET @TABLE_NAME = 'Categories';

DECLARE @COLUMN_NAME NVARCHAR(50); 
SET @COLUMN_NAME = 'Tags';

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE
					TABLE_NAME = @TABLE_NAME)
	BEGIN
		
		PRINT CONCAT('1. Categories table does exists.', 
					CHAR(13), '1.1 Let''s now create the Tags column.');
		
		IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
					WHERE TABLE_NAME = @TABLE_NAME
					AND COLUMN_NAME = @COLUMN_NAME)
			BEGIN
				PRINT '2. Dropping the Tags column of the Categories table.';
				ALTER TABLE [Categories]
				DROP COLUMN [Tags];
			END
		
		BEGIN
			PRINT '3. Creating the Tags column of the Categories table.';

			ALTER TABLE [Categories]
			ADD [Tags] NVARCHAR(MAX);
			
			DECLARE @ADDED_COLUMNS NVARCHAR(MAX);
			
			SET @ADDED_COLUMNS = CONCAT('4. Categories table columns: ',  
										(SELECT STRING_AGG(COLUMN_NAME, ',') 
										FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABLE_NAME));

			PRINT @ADDED_COLUMNS;
		END
	END
ELSE
	BEGIN
		PRINT 'CATEGORY TABLE DOESN''T EXISTS';
	END

Going to the example above, as you can see, we have to check if the Categories table exists. Then from that, we did check if the Tags column does exist. If it does, we need to drop the existing column. And after that, we re-created the Tags column. Lastly, we have shown all the Categories-table columns by a comma-separated list.

Just a note, the STRING_AGG function is a function that can be applied to SQL Server 2017 and later.

Output

Find Foreign Keys, Primary Keys, and Check Constraints in a Table

Let's see how we can query the primary and foreign keys and check the constraint of a specific table.

In this case, we will use the [Order Details] table and show the constraints such as primary key, foreign key, and check constraint.

Let's see the query below.

USE [Northwind];

SELECT CONSTRAINT_TYPE, CONSTRAINT_NAME 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE CONSTRAINT_TYPE IN ('PRIMARY KEY', 'FOREIGN KEY', 'CHECK')
AND TABLE_NAME = 'Order Details';

Querying Views

In this last section, we'll make a simple query showing the Views inside the Northwind database.

Let's see the query below.

USE [Northwind];

SELECT TABLE_CATALOG AS 'Database Name',
	   TABLE_NAME AS 'View Name',
	   View_Definition as 'Query'
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME  LIKE 'Products%'

Summary

I hope you have enjoyed this article. I know that there are many ways you can use INFORMATION_Schema.

Moreover, this article has given you a jump start by showing you how to get all the databases and tables, search for a column, and search for constraints and views using the INFORMATION_Schema.

Once again, I hope you have enjoyed this article/tutorial as I have enjoyed writing it.

Stay tuned for more. Until next time, happy programming!

Please don't forget to bookmark, like, and comment. Cheers! And Thank you!