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,
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
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;
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';
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%'
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!