Che cos’è un dizionario di dati?

In SQL Server il dizionario dei dati è un insieme di tabelle di database utilizzate per memorizzare informazioni sulla definizione di un database. Il dizionario contiene informazioni sugli oggetti del database come tabelle, indici, colonne, tipi di dati e viste.

Il dizionario dei dati viene utilizzato da SQL Server per eseguire query e viene aggiornato automaticamente ogni volta che gli oggetti vengono aggiunti, rimossi o modificati all’interno del database.

Tutti gli esempi di questo articolo sono basati su Microsoft SQL Server Management Studio e il database AdventureWorks2012. È possibile iniziare a utilizzare questi strumenti gratuiti utilizzando la mia guida Per iniziare a utilizzare SQL Server.

Come SQL Server utilizza il dizionario dei dati

SQL Server utilizza il dizionario del database per verificare le istruzioni SQL. Quando si esegue un’istruzione SQL, il DBMS (Database Management System) analizza l’istruzione e quindi determina se le tabelle e i campi a cui si fa riferimento sono validi. Per fare ciò rapidamente fa riferimento al dizionario dei dati.

Oltre a verificare la validità delle istruzioni, SQL Server utilizza il dizionario dei dati per assistere nella generazione del piano di query e per fare riferimento alle informazioni che definiscono la struttura del database.

Il dizionario dei dati diventa un libro guida, di per sé, per SQL Server da utilizzare per accedere ai dati. In poche parole, senza il dizionario dei dati, anche se SQL Server conoscerebbe e capirebbe il linguaggio SQL, non sarebbe a conoscenza delle tabelle e delle colonne del database; pertanto, non sarebbe in grado di interrogarle.

Altri usi per il Dizionario dei dati

Poiché il dizionario dei dati contiene la definizione del database, è una risorsa davvero buona da utilizzare per ottenere informazioni sul database. La cosa davvero interessante è che il dizionario dei dati è costituito da tabelle e viste SQL. Ciò significa che è possibile ottenere informazioni sul DB tramite query!

I dizionari di dati vengono utilizzati dai progettisti e dagli sviluppatori per comprendere la struttura del database. Puoi pensare al dizionario come a un documento di riferimento aggiornato.

Gli strumenti di progettazione come SQL Server Management Studio visualizzano le informazioni sui database tramite esplora oggetti utilizzando il dizionario dati.

Le tabelle sopra elencate non sono magicamente noto, invece, l’oggetto explorer rilasciato una query per il dizionario di dati per recuperare tutte le tabelle utente.

Il dizionario dei dati è memorizzato in una serie di tabelle di sistema. Sebbene sia possibile interrogare direttamente queste tabelle, Microsoft si riserva il diritto di modificare le tabelle di sistema che compongono il dizionario dei dati. Per questo motivo, si consiglia di interrogare le viste INFORMATION_SCHEMA anziché accedere direttamente alle tabelle.

Poiché è possibile interrogare il dizionario dei dati da soli, è possibile rispondere ad alcune domande che altrimenti richiederebbero molta caccia e beccare attraverso object explorer. Ad esempio, come è possibile trovare facilmente tutte le tabelle e le viste utilizzando la colonna BusinessEntityID? Senza il dizionario dei dati, dovrai utilizzare object explorer e aprire ogni tabella e visualizzare e guardare attraverso le definizioni per la colonna. Tuttavia, utilizzando il dizionario dei dati questo può essere fatto utilizzando una semplice query.

Visualizzazioni INFORMATION_SCHEMA

Le visualizzazioni INFORMATION_SCHEMA incluse in SQL Server sono conformi allo standard ISO SQL-92. Ciò significa che altri fornitori di database conformi allo standard ISO forniranno lo stesso set di visualizzazioni.

Ecco alcune delle viste più comunemente utilizzate e le loro descrizioni:

• COLONNE-Restituisce una riga per ogni colonna che l’utente corrente ha accesso da utilizzare nel database corrente. Questa vista può essere utilizzata per determinare il tipo di dati e la tabella che la colonna è definita per l’uso.
• TABELLE-Restituisce una riga per ogni tabella che gli utenti hanno accesso da utilizzare all’interno del database corrente. Nota, entrambe le tabelle e le viste vengono restituite utilizzando la vista TABELLE.
• VIEW_TABLE_USAGE-Restituisce una riga per ogni tabella utilizzata in una vista all’interno del database corrente.
• VISTE-Restituisce una riga per le viste a cui è possibile accedere utilizzando le autorizzazioni utente correnti dal database corrente.

Esempi usando le viste INFORMATION_SCHEMA

Elencando tutte le tabelle che includono una colonna specificata

Puoi farlo usando INFORMATION_SCHEMA.Vista COLONNE. Ad esempio i seguenti elenchi tutte le tabelle e le viste contengono la colonna BusinessEntityID

SELECT TABLE_NAMEFROM AdventureWorks2012_Data.INFORMATION_SCHEMA.COLUMNSWHERE COLUMN_NAME = 'BusinessEntityID'

Come puoi immaginare il dizionario dei dati può tornare utile! Riesci a immaginare di dover cercare ogni definizione a mano per trovare tutte le occorrenze di un campo all’interno del database?

Elenca tutte le tabelle in un database

Usa INFORMATION_SCHEMA.Vista TABELLE per fare questo.

SELECT TABLE_NAME, TABLE_TYPEFROM AdventureWorks2012_Data.INFORMATION_SCHEMA.TABLESORDER BY TABLE_NAME

Questa query restituisce sia le tabelle di base che le viste.

Elenca il numero di tabelle in ogni schema

In questo esempio sono elencati ogni schema e il numero di tabelle e viste definite al loro interno:

SELECT TABLE_SCHEMA, Count(TABLE_SCHEMA)FROM AdventureWorks2012_Data.INFORMATION_SCHEMA.TABLESGROUP BY TABLE_SCHEMA

Viste catalogo SQL Server

Le viste catalogo sono un altro modo per visualizzare il dizionario dei dati. Ritengo che le viste del catalogo degli oggetti forniscano maggiori informazioni e tendo a utilizzare queste viste sulle viste INFORMATION_SCHEMA.

Alcune delle viste che ho trovato più utili includono:

• oggetti – contengono una riga per ogni oggetto, come una CHIAVE ESTERNA o un vincolo di CHIAVE PRIMARIA definito all’interno del database.
• colonne-contengono una riga per ogni colonna di un oggetto come vista o tabelle.
• tabelle – restituisce una riga per ogni oggetto tabella

Esempi utilizzando le viste catalogo

I seguenti esempi utilizzano le viste catalogo Microsoft SQL Server per ottenere le stesse informazioni utilizzate in precedenza utilizzando INFORMATION_SCHEMA.

Elenco di tutte le tabelle che includono una colonna specificata

Al fine di ottenere i nomi delle tabelle utilizzando una colonna specifica, sia il sys.tabelle e sistemi.la vista colonne deve essere utilizzata insieme. Vengono uniti tramite object_id, che viene utilizzato per identificare oggetti di database comuni come tabelle e viste.

SELECT t.name, t.type_descFROM AdventureWorks2012_Data.sys.columns AS cINNER JOIN sys.tables AS t ON c.object_id = t.object_idWHERE c.name = 'BusinessEntityID'

Elenca tutte le tabelle in un database

Il sys.vista tabelle può essere utilizzato per ottenere i nomi di tutte le tabelle definite nel database. Questa vista restituisce sia le tabelle di base che le viste.

SELECT name, type_descFROM AdventureWorks2012_Data.sys.tablesORDER BY Name

Elenca il numero di tabelle in ogni schema

Il sys.la vista tabelle non include un nome schema, quindi viene utilizzata la funzione SCHEMA_NAME integrata per ottenerla.

SELECT SCHEMA_NAME(schema_id), count(name)FROM AdventureWorks2012_Data.sys.tablesGROUP BY SCHEMA_NAME(schema_id)

Come puoi vedere dall’esempio, è un po ‘ più tecnico rispetto all’utilizzo di INFORMATION_SCHEMA. Tuttavia, dico per esperienza che ci sono molte più informazioni contenute nelle viste del catalogo-Se hai una particolare domanda sulla struttura di un database, queste sono le tabelle da colpire.