#information_schema

Le viste Information Schema sono uno delle tante modalità offerte da SQL Server per ottenere dei metadati. Il vantaggio di utilizzare tale vista è che è presente in quasi tutti i RDMS (è uno standard ANSI), per esempio Oracle, MySQL e Postgres lo supportano.

Queste viste hanno il vantaggio di fornire informazioni indipendenti dalle tabelle interne utilizzate da SQL server o dal RDBMS di riferimento, stando quindi ad un livello di astraziomne superiore.

In questo post approfondiremo le tre principali viste di Information Schema, cioò quelle riguardanti le tabelle, le viste e le colonne

TABLES

Fornisce una riga per ogni tabella nel database corrente, in particolare fornisce le seguenti colonne:

TABLE_CATALOG: Nome del database

TABLE_SCHEMA: Nome dello schema che contiene la tabella

TABLE_NAME: Nome della tabella

TABLE_TYPE: Tipo della tabella, può essere o VIEW o BASE TABLE

COLUMNS

Fornisce una riga per ogni colonna nel database corrente, in particolare fornisce le seguenti colonne (indico solo le principali)

TABLE_CATALOG: Nome del database

TABLE_SCHEMA: Nome dello schema che contiene la tabella

TABLE_NAME: Nome della tabella

COLUMN_NAME: Nome della colonna

ORDINAL_POSITION: Numero identificativo della stessa

COLUMN_DEFAULT: Valore di default

IS_NULLABLE: YES se la colonna permette anche valori NULL, NO altrimenti

VIEWS

Fornisce una riga per ogni vista nel database corrente, in particolare fornisce le seguenti colonne (indico solo le principali)

TABLE_CATALOG: Nome del database

TABLE_SCHEMA: Nome dello schema che contiene la tabella

TABLE_NAME: Nome della tabella

VIEW_DEFINITION: Fornisce la definizione della vista, solo se è lunga meno di 4000 caratteri. Altrimenti NULL

Esempi

Di seguito qualche query che utilizza INFORMATION_SCHEMA

Ottenere informazioni di una determinata tabella

SELECT * FROM INFORMATION_SCHEMA.TABLES --VIEWS, COLUMNS WHERE TABLE_NAME = N'foo'

Ottenere informazioni per la stessa tabella su tutti i database

Per ottenere invece l'informazione su tutti i database e non solo il corrente è necessario utilizzare la procedura sp_msforeachdb con la sys.tables nel seguente modo:

sp_msforeachdb 'SELECT "?" AS dbname, * FROM [?].sys.tables WHERE name="foo"'

Controllare l'esistenza di una vista

Esistono varie query per verificare l'esistenza di una vista (ed analogamente ogni altro oggetto), la soluzione più portabile in quanto (quasi) agnostica al tipo di database è la seguente:

MySQL(InnoDB)で外部キーを利用されているテーブルを抽出。 MySQL Workbench であるデータベースを見ると、リレーションが一個もなかったので、念のためにね。

MySQL 5.5 Reference Manual :: 14 Storage Engines :: 14.3 The InnoDB Storage Engine :: 14.3.5 Creating and Using InnoDB Tables :: 14.3.5.4 FOREIGN KEY Constraints

SELECT referenced_table_name, table_name, constraint_name FROM information_schema.key_column_usage WHERE referenced_table_name IS NOT NULL ORDER BY referenced_table_name;

information_schema.KEY_COLUMN_USAGE から情報を取得