Introduzione ad INFORMATION_SCHEMA

Data pubblicazione: 08 ottobre 2022

Introduzione ad INFORMATION_SCHEMA

INFORMATION_SCHEMA è un insieme di viste standard ANSI e, come tale, lo troviamo sia in Microsoft Sql Server ma anche in tutti gli altri motori SQL che aderiscono a tale standard.

Questo insieme di viste ci forniscono informazioni, o potremmo definirli metadati, sugli oggetti definiti nei database installati.

Le informazioni fornite riguardano le tabelle, le relative colonne, le viste, le procedure (stored procedure e functions), i vincoli d'integrità e molto altro. In questo articolo vedremo esempi pratici che permetteranno di comprenderne meglio l'utilizzo.

Definizione dei database sui quali utilizzare INFORMATION_SCHEMA

Prima di vedere gli esempi, creiamo due database che utilizzeremo per sperimentare le funzionalità di INFORMATION_SCHEMA. I due database li chiamiamo DB_Source e DB_Target e lo script per la creazione è riportato di seguito:

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

All'interno del database DB_Source definiamo le tabelle tramite questo 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

La struttura del database DB_Source è, pertanto, quella riportata nell'immagine che segue:

In modo analogo, nel database DB_Target aggiungiamo le tabelle tramite lo 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

e, quindi, la sua struttura è quella raffigurata di seguito:

Come si può notare, le due strutture sono simili tra loro. Le differenze sono:

nel database DB_Target è presenta la tabella Turni;
la tabella Reparti ha la colonna Descrizione nel database DB_Source, mentre in DB_Target ha la colonna Ubicazione;
la tabella Utenti nel database DB_Target ha i campi CodiceFiscale e ID_Turno, non definiti nella stessa tabella del database DB_Source.

A questo punto siamo pronti per fare alcuni esperimenti con le viste di INFORMATION_SCHEMA.

Accedere alle informazioni sulle tabelle: INFORMATION_SCHEMA.TABLES

Partiamo con un esempio che utilizza la vista TABLES, la quale permette di recuperare l'elenco delle tabelle definite in un database:

USE [DB_Source]

SELECT *
FROM INFORMATION_SCHEMA.TABLES

USE [DB_Target]

SELECT *
FROM INFORMATION_SCHEMA.TABLES

Il risultato della query è il seguente:

Per entrambi i database, abbiamo ottenuto l'elenco delle tabelle create in precedenza. A queste si è aggiunta la tabella sysdiagrams: è la tabella creata da SQL Server Management Studio per memorizzare i dati dei diagrammi che ho creato in precedenza per mostrare le strutture dei due database.

Le informazioni che ci fornisce la vista INFORMATION_SCHEMA.TABLES sono:

Colonna	Descrizione
TABLE_CATALOG	Nome del database
TABLE_SCHEMA	Nome dello schema nel quale è definita la tabella o la vista
TABLE_NAME	Nome della tabella o della vista
TABLE_TYPE	Tipo della tabella. Può essere `BASE TABLE` per le tabelle oppure `VIEW` per le viste

Come si può evincere dai valori che può assumere TABLE_TYPE, la vista INFORMATION_SCHEMA.TABLES ci fornisce informazioni anche sulle viste definite nel database. Aggiungiamo una vista al database DB_Source con il seguente script, che ci fornisce i dati della tabella Utenti e Reparti in forma appiattita:

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

Eseguiamo di nuovo la query sulla vista TABLES:

USE [DB_Source]

SELECT *
FROM INFORMATION_SCHEMA.TABLES

Il risultato della query ci mostra anche i dati della vista che abbiamo appena aggiunto:

Accedere alle informazioni sulle colonne: INFORMATION_SCHEMA.COLUMNS

La vista INFORMATION_SCHEMA.COLUMNS, come si può intuire dal suo nome, ci fornisce le informazioni sulle colonne definite in tabelle o viste all'interno di un database. Poiché questa vista ci fornisce un buon numero di dati sulle colonne, scegliamo di visualizzare solo alcuni di questi:

USE [DB_Source]

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

Il risultato della query è il seguente:

Come abbiamo già visto in precedenza, anche interrogando la vista INFORMATION_SCHEMA.COLUMNS vediamo i dati relativi alla tabella sysdiagrams, ovvero in qeusto caso vediamo le colonne che la compongono.

Vediamo quali sono le informazioni principali che possiamo consultare attraverso la vista INFORMATION_SCHEMA.COLUMNS:

Colonna	Descrizione
TABLE_CATALOG	Nome del database
TABLE_SCHEMA	Nome dello schema nel quale è definita la tabella o la vista
TABLE_NAME	Nome della tabella o della vista
COLUMN_NAME	Nome della colonna
ORDINAL_POSITION	Posizione della colonna all'interno della tabella o della vista
COLUMN_DEFAULT	Valore di default (predefinito) della colonna
IS_NULLABLE	Il valore è `YES` se la colonna può assumere il valore `null`, altrimenti vale `NO`
DATA_TYPE	E' il tipo di dato della colonna (int, varchar, datetime, varbinary, ...)
CHARACTER_MAXIMUM_LENGTH	E' la lunghezza massima del campo espressa in numero di caratteri (il valore è definito per i campi di tipo binario, carattere, text o image, per le colonne di altro tipo è `null`)
NUMERIC_PRECISION	Precisione dei dati di tipo numerico. Per gli altri tipi di dati viene restituito `null`
NUMERIC_SCALE	Scala dei dati di tipo numerico. Per gli altri tipi di dati viene restituito `null`

Per avere informazioni su tutti gli altri dati forniti dalla vista INFORMATION_SCHEMA.COLUMNS, rimando alla documentazione ufficiale

Nel prossimo articolo, vedremo come sfruttare queste viste per creare script di utilità, ad esempio per confrontare le strutture di due database ed evidenziarne le differenze.