• Articolo

Esecuzione di query nel catalogo di sistema di SQL Server

Nelle applicazioni dinamiche per le quali non è previsto l'utilizzo di un gruppo di tabelle e viste specifico deve essere disponibile un meccanismo per l'individuazione della struttura e degli attributi degli oggetti contenuti nei database a cui le applicazioni si connettono. Queste applicazioni possono richiedere informazioni come ad esempio:

  • Il numero e i nomi delle tabelle e viste di un database.

  • Il numero di colonne di una tabella o vista, insieme a nome, tipo di dati, scala e precisione di ogni colonna.

  • I vincoli definiti per una tabella.

  • Gli indici e le chiavi definiti per una tabella.

Il catalogo di sistema fornisce queste informazioni per i database di SQL Server. L'elemento chiave dei cataloghi di sistema di SQL Server è un set di viste che mostra i metadati che descrivono gli oggetti in un'istanza di SQL Server. I metadati sono dati che descrivono gli attributi degli oggetti di un sistema. Le applicazioni basate su SQL Server possono accedere alle informazioni dei cataloghi di sistema utilizzando gli elementi seguenti:

  • Viste del catalogo. Si tratta del metodo di accesso consigliato.

  • Viste degli schemi delle informazioni.

  • Set di righe dello schema OLE DB.

  • Funzioni del catalogo ODBC.

  • Stored procedure e funzioni di sistema.

Le viste del catalogo offrono accesso ai metadati archiviati in ogni database sul server.

[!NOTA]

Le viste del catalogo non offrono accesso alla replica, a SQL Server Agent o ai metadati di backup.

È consigliabile utilizzare le viste del catalogo per accedere ai metadati per le ragioni seguenti:

  • Tutti i metadati sono resi disponibili come viste del catalogo.

  • Le viste del catalogo presentano i metadati in un formato indipendente dall'implementazione delle tabelle del catalogo, pertanto non sono influenzate dalle modifiche apportate alle tabelle del catalogo sottostanti.

  • Le viste del catalogo rappresentano il modo più efficiente per accedere ai metadati del server essenziali.

  • Le viste del catalogo sono l'interfaccia generale per i metadati del catalogo e costituiscono il modo più diretto per ottenere, trasformare e presentare questi metadati in modo personalizzato.

  • I nomi delle viste del catalogo e delle relative colonne sono descrittivi. I risultati delle query corrispondono a quanto prevedibile da un utente con discreta conoscenza della funzionalità corrispondente ai metadati sui quali eseguire la query.

Ad esempio, nella seguente query viene utilizzata la vista del catalogo sys.objects per restituire tutti gli oggetti di database modificati negli ultimi 10 giorni.

SELECT name AS object_name 
  ,SCHEMA_NAME(schema_id) AS schema_name
  ,type_desc
  ,create_date
  ,modify_date
FROM sys.objects
WHERE modify_date > GETDATE() - 10
ORDER BY modify_date;

Per ulteriori esempi relativi all'esecuzione di query nel catalogo di sistema tramite le viste del catalogo, vedere Domande frequenti sull'esecuzione di query sul catalogo di sistema di SQL Server.

Nota importanteImportante

Nelle versioni future di SQL Server è possibile che Microsoft estenda la definizione delle viste del catalogo di sistema aggiungendo colonne all'elenco delle colonne. Non è consigliabile utilizzare la sintassi SELECT * FROM sys.catalog_view_name nel codice di produzione. Il numero di colonne restituite potrebbe infatti cambiare compromettendo il corretto funzionamento dell'applicazione.

Viste degli schemi delle informazioni

Le viste degli schemi delle informazioni sono basate sulle definizioni delle viste del catalogo nello standard ISO e presentano le informazioni in un formato indipendente dall'implementazione delle tabelle del catalogo. Le modifiche apportate alle tabelle quindi non hanno alcun effetto sulle viste. Le applicazioni che utilizzano queste viste sono supportate in sistemi di database eterogenei compatibili con ISO. Per ulteriori informazioni, vedere Viste degli schemi delle informazioni (Transact-SQL).

[!NOTA]

Le viste degli schemi delle informazioni non includono metadati specifici per SQL Server 2008.

Nel seguente esempio viene eseguita una query alla vista INFORMATION_SCHEMA.COLUMNS per ottenere tutte le colonne per la tabella Contact nel database AdventureWorks.

SELECT TABLE_NAME, COLUMN_NAME, COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') AS COLUMN_ID
FROM AdventureWorks.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Contact';

Viste di compatibilità

Molte delle tabelle di sistema in versioni precedenti di SQL Server sono ora implementate come set di viste. Queste viste sono note come viste di compatibilità e sono rese disponibili solo per compatibilità con le versioni precedenti. Esse mostrano gli stessi metadati disponibili in SQL Server 2000. Tuttavia, non espongono i metadati correlati a funzionalità introdotte in SQL Server 2005 e versioni successive. Pertanto, quando si utilizzano le nuove funzioni, ad esempio Service Broker o il partizionamento, è necessario passare all'utilizzo delle viste del catalogo. Si tratta di un buon motivo per eseguire l'aggiornamento a questa funzione. Un'altra ragione per farlo è che le colonne delle viste di compatibilità nelle quali sono archiviati gli ID utente e tipo possono restituire overflow aritmetici NULL o di trigger. Ciò avviene poiché in SQL Server 2005 e versioni successive è possibile creare oltre 32.767 utenti e tipi di dati. Ad esempio, se sono stati creati 32.768 utenti e quindi si esegue la query seguente: SELECT * FROM sys.sysusers, se ARITHABORT è impostato su ON, la query genera un errore di overflow aritmetico. Se ARITHABORT è impostato su OFF, la colonna UID restituisce NULL.

Per evitare questi problemi, è consigliabile passare a utilizzare le nuove viste del catalogo, in grado di gestire il maggior numero di ID utente e tipi.

Set di righe dello schema OLE DB

La specifica OLE DB definisce un'interfaccia IDBSchemaRowset che espone un gruppo di set di righe dello schema contenenti le informazioni del catalogo. I set di righe dello schema OLE DB rappresentano un metodo standard per la presentazione delle informazioni del catalogo supportato da diversi provider OLE DB. I set di righe sono indipendenti dalla struttura delle tabelle del catalogo. Per ulteriori informazioni, vedere Supporto dei set di righe dello schema (OLE DB).

Il provider OLE DB per Microsoft SQL Server Native Client supporta un'estensione di IDBSchemaRowset per la visualizzazione delle informazioni del catalogo relative ai server collegati utilizzati nelle query distribuite. Per ulteriori informazioni, vedere Set di righe LINKEDSERVERS (OLE DB).

Funzioni del catalogo ODBC

La specifica ODBC definisce un gruppo di funzioni del catalogo che restituiscono set di risultati contenenti le informazioni relative al catalogo. Queste funzioni rappresentano un metodo standard per la presentazione delle informazioni del catalogo supportato da diversi driver ODBC. I set di risultati sono indipendenti dalla struttura delle tabelle del catalogo sottostanti.

Il driver ODBC di SQL Server Native Client supporta due funzioni specifiche del driver per la visualizzazione delle informazioni del catalogo relative ai server collegati utilizzati nelle query distribuite. Per ulteriori informazioni, vedere Utilizzo delle funzioni di catalogo.

Stored procedure e funzioni di sistema

Transact-SQL definisce stored procedure di sistema e funzioni di sistema che restituiscono informazioni del catalogo. Questi elementi, pur essendo specifici di SQL Server, consentono di isolare gli utenti dalla struttura delle tabelle del catalogo di sistema sottostanti. Per ulteriori informazioni, vedere Funzioni per i metadati (Transact-SQL) e Stored procedure di sistema (Transact-SQL).