Le proprietà di un oggetto come una tabella o una vista sono memorizzate in una tabella di sistema speciale. Questi attributi sono chiamati metadati. Tutti gli oggetti SQL hanno metadati. Questi metadati sono visualizzabili dalle viste di sistema, che sono viste predefinite di SQL Server

Esistono più di 230 diverse viste di sistema e vengono automaticamente aggiunte al database creato dall'utente. Queste viste sono raggruppate in diversi schemi come segue.

Viste del catalogo di sistema

Queste viste contengono informazioni sul sommario all'interno del sistema SQL Server. I cataloghi sono simili all'inventario o agli oggetti. Queste viste contengono una grande quantità di metadati. Nelle versioni precedenti di SQL Server, per ottenere questi dati gli utenti dovevano eseguire query su grandi quantità di dati in tabelle di sistema, visualizzazioni di sistema e funzioni di sistema. Dalla versione 2021 in poi, tutti gli utenti possono facilmente eseguire query sui metadati del catalogo per la ricerca.

Per esempio:

 SELECT name,object_id,type,type_desc FROM sys.tables;

Viste dello schema di informazioni

Gli utenti possono interrogare le informazioni sullo schemaview e restituire i metadati di sistema. Queste viste sono utili quando si comunica con terze parti. Le informazioni sulle visualizzazioni dello schema consentono alle applicazioni di funzionare correttamente nonostante le modifiche significative alle tabelle di sistema sottostanti.

La tabella seguente ci aiuterà a decidere se eseguire query su viste di sistema specifiche di SQL Server o viste schema di informazioni:

Per esempio:

 SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES;

Funzioni dei metadati di sistema

Oltre alle visualizzazioni, SQL Server offre diverse funzioni predefinite che restituiscono metadati per una query. Questi includono funzioni scalari e con valori di tabella, che possono restituire informazioni su impostazioni di sistema, preferenze di sessione e una varietà di altri oggetti.

Le funzioni di metadati di SQL Server sono disponibili in diversi formati, alcune sembrano simili alle funzioni scalari standard, ad esempio ERROR_NUMBER(). Altre funzioni utilizzano prefissi speciali, come @@VERSION o $PARTITION.

Esempio di utilizzo dell'istruzione SELECT per interrogare i metadati di sistema:

 SELECT SERVERPROPERTY('EDITION') AS EditionName;

Una delle nuove funzionalità di SQL Server 2019 sono i metadati tempdb ottimizzati per la memoria. Il team di SQL Server ha migliorato il codice tempdb con ottimizzazioni in modo che alcuni metadati che possono costituire un collo di bottiglia nei sistemi tempdb pesanti ora possano fare affidamento sulla memoria e siano ottimizzati per l'accesso alla RAM.

Gli ambienti ad alto volume e su larga scala che utilizzano molto tempdb spesso presentano questo tipo di collo di bottiglia, prima di ciò sarebbe necessario un qualche tipo di soluzione per ridurre l'utilizzo di tempdb. Tuttavia, con questa nuova funzionalità, è possibile consentire ai metadati di rimanere in memoria e di accedervi in modo ottimale.

Query oggetto gestita dinamicamente

Introdotte per la prima volta da SQL Server 2006, le viste a gestione dinamica (DMV) e le funzioni di gestione dinamica (DMF) sono oggetti a gestione dinamica che restituiscono informazioni sul server o informazioni sullo stato del database. DMV e DMF sono indicati collettivamente come oggetti gestiti dinamici. Forniscono informazioni dettagliate sulle operazioni del software e possono essere usati per controllare lo stato delle istanze di SQL Server, risolvere i problemi e ottimizzare le prestazioni.

Sia i DMV che i DMF restituiscono dati tabulari, ma la differenza è che il DMF accetta almeno un parametro, mentre il DMV non accetta parametri. SQL Server 2019 offre quasi 200 oggetti a gestione dinamica. Per interrogare i DMV, sarà richiesta l'autorizzazione VIEW SERVER STATE o VIEW DATABASE STATE, in base all'ambito del DMV.

Catalogazione e interrogazione di DMV

Elenco di convenzioni per aiutare a organizzare i DMV utilizzando la funzione

Per interrogare un oggetto a gestione dinamica, utilizzare l'istruzione SELECT come si farebbe con qualsiasi oggetto vista o tabella multivalore. Esempio: il codice seguente restituisce un elenco di connessioni utente correnti da sys.dm_ex .

sys.dm_exec_sessions è un DMV con ambito server che visualizza informazioni su tutte le connessioni utente attive e le azioni interne. Queste informazioni includono l'utente che ha effettuato l'accesso, le impostazioni della sessione corrente, la versione del client, il nome del programma del client, l'ora di accesso del client, ecc. sys.dm_exec_sessions può essere utilizzato per identificare una sessione specifica e trovare informazioni su tale sessione.

Per esempio:

 SELECT session_id, login_time, program name FROM sys.dm_exec_sessions WHERE login_name='sa' and is_user_process = 1;

Qui, is_user_process è una colonna nella vista che determina se la sessione è una sessione di sistema o meno. Un valore di 1 indica che non si tratta di una sessione di sistema ma di una sessione utente. La colonna nome_programma definisce il nome del programma client che ha avviato la sessione, la colonna ora_accesso imposta l'ora di inizio della sessione.