Blog | Programming Notes

Blog ❯ Author: Fabio Moschini|Date: 08.10.2022

Introduction to INFORMATION_SCHEMA

DatabaseSql

Introduction to INFORMATION_SCHEMA

INFORMATION_SCHEMA is a set of standard ANSI views, and as such, we find it both in Microsoft SQL Server and in all other SQL engines that adhere to this standard. This set of views provides us with information, or what we could define as metadata, about the objects defined in the installed databases. The information provided concerns tables, their columns, views, procedures (stored procedures and functions), integrity constraints, and much more. In this article, we'll see practical examples that will help better understand its use.

Defining the databases to use with INFORMATION_SCHEMA

Before looking at the examples, let's create two databases that we'll use to experiment with INFORMATION_SCHEMA functionalities. We'll call the two databases DB_Source and DB_Target, and the script for their creation is shown below:

USE [master]
GO

IF DB_ID (N'DB_Source') IS NULL
CREATE DATABASE DB_Source;
GO 

IF DB_ID (N'DB_Target') IS NULL
CREATE DATABASE DB_Target;
GO

Inside the DB_Source database, let's define the tables using this script:

USE [DB_Source]
GO

CREATE TABLE [dbo].[Reparti](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Codice] [varchar](50) NULL,
  [Descrizione] [varchar](50) NULL,
  CONSTRAINT [PK_Reparti] PRIMARY KEY CLUSTERED
    (
      [ID] ASC
    )
) ON [PRIMARY]
GO
 
CREATE TABLE [dbo].[Utenti](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Nome] [varchar](50) NULL,
  [Cognome] [varchar](50) NULL, 
  [DataNascita] [datetime] NULL, 
  [ID_Reparto] [int] NULL, 
  CONSTRAINT [PK_Utenti] PRIMARY KEY CLUSTERED
    (
      [ID] ASC
    )
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Utenti] WITH CHECK ADD CONSTRAINT [FK_Utenti_Reparti] FOREIGN KEY([ID_Reparto])
  REFERENCES [dbo].[Reparti] ([ID])
GO

ALTER TABLE [dbo].[Utenti] CHECK CONSTRAINT [FK_Utenti_Reparti]
GO

Therefore, the structure of the DB_Source database is shown in the following image:

Similarly, in the DB_Target database, we'll add the tables using this script:

USE [DB_Target]
GO

CREATE TABLE [dbo].[Reparti](
  [ID] [int] IDENTITY(1,1) NOT NULL, 
  [Codice] [varchar](50) NULL, 
  [Ubicazione] [varchar](50) NULL, 
  CONSTRAINT [PK_Reparti] PRIMARY KEY CLUSTERED 
    (
      [ID] ASC
    )
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Turni](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Codice] [varchar](50) NULL,
  [OraInizio] [tinyint] NULL,
  [OraFine] [tinyint] NULL,
  CONSTRAINT [PK_Turni] PRIMARY KEY CLUSTERED
    (
      [ID] ASC 
    ) 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Utenti](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Nome] [varchar](50) NULL, 
  [Cognome] [varchar](50) NULL, 
  [DataNascita] [datetime] NULL, 
  [CodiceFiscale] [varchar](50) NULL, 
  [ID_Reparto] [int] NULL, 
  [ID_Turno] [int] NULL, 
  CONSTRAINT [PK_Utenti] PRIMARY KEY CLUSTERED
    (
      [ID] ASC 
    ) 
) ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Utenti] WITH CHECK ADD CONSTRAINT [FK_Utenti_Reparti] FOREIGN KEY([ID_Reparto])
  REFERENCES [dbo].[Reparti] ([ID])
GO

ALTER TABLE [dbo].[Utenti] CHECK CONSTRAINT [FK_Utenti_Reparti]
GO

ALTER TABLE [dbo].[Utenti] WITH CHECK ADD CONSTRAINT [FK_Utenti_Turni] FOREIGN KEY([ID_Turno])
  REFERENCES [dbo].[Turni] ([ID])
GO 

ALTER TABLE [dbo].[Utenti] CHECK CONSTRAINT [FK_Utenti_Turni] 
GO

and therefore, its structure is shown in the following diagram:

As you can see, the two structures are similar to each other. The differences are:

- in the DB_Target database, there is theTurni (Shifts) table;
- the Reparti (Departments) table has the column Descrizione (Description) in the DB_Source database, while in DB_Target it has the column Ubicazione (Location);
- the Utenti (Users) table in the DB_Target database has the fields CodiceFiscale (Fiscal Code) and ID_Turno (Shift ID), not defined in the same table of the DB_Source database.

At this point, we're ready to do some experiments with INFORMATION_SCHEMA views.

Accessing table information: INFORMATION_SCHEMA.TABLES

Let's start with an example using the TABLES view, which allows us to retrieve the list of tables defined in a database:

USE [DB_Source]

SELECT * 
FROM INFORMATION_SCHEMA.TABLES

USE [DB_Target]

SELECT *
FROM INFORMATION_SCHEMA.TABLES

The result of the query is as follows:

For both databases, we obtained the list of tables created previously.
The sysdiagrams table was added: it's the table created by SQL Server Management Studio to store the data of the diagrams that I created earlier to show the structures of the two databases.
The information provided by the INFORMATION_SCHEMA.TABLES view is:

Column	Description
TABLE_CATALOG	Database name
TABLE_SCHEMA	Name of the schema in which the table or view is defined
TABLE_NAME	Name of the table or view
TABLE_TYPE	Type of the table. It can be BASE TABLE for tables or VIEW for views

As can be deduced from the values that TABLE_TYPE can take, the INFORMATION_SCHEMA.TABLES view also provides us with information about the views defined in the database.
Let's add a view to the DB_Source database with the following script, which provides us with the data from the Users and Departments tables in a flattened form:

USE [DB_Source]
GO

CREATE VIEW [dbo].[Utenti_Reparti]
AS
SELECT dbo.Utenti.Nome, dbo.Utenti.ID, dbo.Utenti.Cognome,
       dbo.Utenti.DataNascita, dbo.Reparti.Codice, dbo.Reparti.Descrizione
FROM   dbo.Reparti 
  INNER JOIN dbo.Utenti
    ON dbo.Reparti.ID = dbo.Utenti.ID_Reparto GO

Let's run the query on the TABLES view again:

USE [DB_Source]
        
SELECT * 
FROM INFORMATION_SCHEMA.TABLES

The query result shows us also the data of the view we just added:

Accessing column information: INFORMATION_SCHEMA.COLUMNS

The INFORMATION_SCHEMA.COLUMNS view, as can be inferred from its name, provides us with information about the columns defined in tables or views within a database. Since this view provides us with a good number of data about the columns, let's choose to display only some of these:

USE [DB_Source]

SELECT TABLE_CATALOG, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION 
FROM INFORMATION_SCHEMA.COLUMNS

The result of the query is as follows:

As we've seen before, even when querying the INFORMATION_SCHEMA.COLUMNS view, we see the data related to the sysdiagrams table, or in this case, we see the columns that compose it.
Let's see what are the main pieces of information we can consult through the INFORMATION_SCHEMA.COLUMNS view:

Column	Description
TABLE_CATALOG	Database name
TABLE_SCHEMA	Name of the schema in which the table or view is defined
TABLE_NAME	Name of the table or view
COLUMN_NAME	Name of the column
ORDINAL_POSITION	Position of the column within the table or view
COLUMN_DEFAULT	Default value of the column
IS_NULLABLE	The value is YES if the column can take a null value, otherwise it's NO
DATA_TYPE	It's the data type of the column (int, varchar, datetime, varbinary, ...)
CHARACTER_MAXIMUM_LENGTH	It's the maximum length of the field expressed in number of characters (the value is defined for binary, character, text or image fields, for columns of other types it is null)
NUMERIC_PRECISION	Precision of numeric data types. For other data types, null is returned
NUMERIC_SCALE	Scale of numeric data types. For other data types, null is returned

For information about all the other data provided by the INFORMATION_SCHEMA.COLUMNS view, I refer you to the official documentation.
In the next article, we'll see how to leverage these views to create utility scripts, for example to compare the structures of two databases and highlight their differences.