Blog | Programming Notes

Blog ❯ Autore: Fabio Moschini|Data: 08.10.2022

Introduzione ad INFORMATION_SCHEMA

DatabaseSql

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.