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 come across 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 came to the right place.

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

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

What is INFORMATION_SCHEMA Database?

The INFORMATION_SCHEMA stores other databases' details on the server.

It gives you the chance 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,

information_schema

Benefits

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

Common Usages

Before we start, we'll be using 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 the last query some views.

Showing All Databases

Let's try to show first all of the databases on a current server instance that we're in.

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

information_schema_show_database

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

However, it is an excellent start for us as we go through from database to tables to columns to keys.

Showing All Tables

Now that we have an idea of getting the database on a SQL Server instance.

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

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

information_schema_all_tables

Querying Column of a Specific Table

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

Then add a new column to the categories table. Then let's set Tags as its column name and set 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 have 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 Key, and Check Constraint in a Table

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

In this case, we're just going to 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';

information_schema_constraints

Querying Views

In this last section, we'll make a simple query that will show 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%'

information_schema_views

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, get all the tables, search for a column, search for constraints and search for 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!