Apa itu INFORMATION SCHEMA?

posted Apr 15, 2012, 8:05 PM by Webmaster BelajarSQL.com   [ updated May 15, 2015, 10:48 AM ]
 

Pendahuluan

Information Schema adalah suatu standar ANSI SQL untuk kumpulan view atau table - tergantung implementasinya - yang dapat digunakan untuk melihat informasi mengenai metadata dari suatu database, table, view, dan column.

View-view dapat diakses dengan menggunakan prefix INFORMATION_SCHEMA. Sebagai contoh, untuk mengakses view yang berisi informasi mengenai column maka kita menggunakan INFORMATION_SCHEMA.COLUMNS.

Berikut adalah contoh daftar view selengkapnya dari beberapa database relasional (SQL Server 2008 / 2008 R2, MySQL 5.x, dan PostgreSQL 8 / 9).

Daftar View INFORMATION_SCHEMA SQL Server 2008

Pada SQL Server 2008, Information Schema diimplementasikan dalam bentuk view. Query berikut digunakan untuk melihat daftar view yang termasuk dalam Information Schema.
Select s2.name as [SCHEMA_NAME], s1.[name] as [VIEW_NAME] from sys.all_views s1 join sys.schemas s2 on s1.schema_id = s2.schema_id where s1.schema_id = 3 order by s1.[name] asc;
Dan berikut adalah daftar schema dan view dari hasil eksekusi tersebut.

SCHEMA_NAME             VIEW_NAME
================================================
INFORMATION_SCHEMA CHECK_CONSTRAINTS
INFORMATION_SCHEMA COLUMN_DOMAIN_USAGE
INFORMATION_SCHEMA COLUMN_PRIVILEGES
INFORMATION_SCHEMA COLUMNS
INFORMATION_SCHEMA CONSTRAINT_COLUMN_USAGE
INFORMATION_SCHEMA CONSTRAINT_TABLE_USAGE
INFORMATION_SCHEMA DOMAIN_CONSTRAINTS
INFORMATION_SCHEMA DOMAINS
INFORMATION_SCHEMA KEY_COLUMN_USAGE
INFORMATION_SCHEMA PARAMETERS
INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS
INFORMATION_SCHEMA ROUTINE_COLUMNS
INFORMATION_SCHEMA ROUTINES
INFORMATION_SCHEMA SCHEMATA
INFORMATION_SCHEMA TABLE_CONSTRAINTS
INFORMATION_SCHEMA TABLE_PRIVILEGES
INFORMATION_SCHEMA TABLES
INFORMATION_SCHEMA VIEW_COLUMN_USAGE
INFORMATION_SCHEMA VIEW_TABLE_USAGE
INFORMATION_SCHEMA VIEWS

Daftar Table INFORMATION_SCHEMA MySQL 5

Pada MySQL, Information Schema diimplementasikan dalam bentuk table. Dan untuk melihat daftar table Information Schema pada MySQL 5.5.16 kita menggunakan perintah berikut.

USE information_schema;

SHOW TABLES;

Dan berikut adalah daftar table hasil eksekusi perintah tersebut.

Tables_in_information_schema
================================================ 
CHARACTER_SETS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
COLUMNS
COLUMN_PRIVILEGES
ENGINES
EVENTS
FILES
GLOBAL_STATUS
GLOBAL_VARIABLES
KEY_COLUMN_USAGE
PARAMETERS
PARTITIONS
PLUGINS
PROCESSLIST
PROFILING
REFERENTIAL_CONSTRAINTS
ROUTINES
SCHEMATA
SCHEMA_PRIVILEGES
SESSION_STATUS
SESSION_VARIABLES
STATISTICS
TABLES
TABLESPACES
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TRIGGERS
USER_PRIVILEGES
VIEWS
INNODB_CMP_RESET
INNODB_TRX
INNODB_CMPMEM_RESET
INNODB_LOCK_WAITS
INNODB_CMPMEM
INNODB_CMP
INNODB_LOCKS

Daftar Table INFORMATION_SCHEMA PostgreSQL

Sama seperti pada MySQL, Information Schema di PostgreSQL juga diimplementasikan dalam bentuk table. Untuk melihat daftar table selengkapnya dari schema tersebut kita melakukan query sebagai berikut.

select table_schema, table_name from information_schema.tables where table_schema = 'information_schema' order by table_name;

Dan hasil daftar table eksekusi tersebut dapat terlihat di bawah ini.

table_schema table_name
==========================================
information_schema _pg_foreign_data_wrappers
information_schema _pg_foreign_servers
information_schema _pg_foreign_tables
information_schema _pg_user_mappings
information_schema administrable_role_authorizations
information_schema applicable_roles
information_schema attributes
information_schema character_sets
information_schema check_constraint_routine_usage
information_schema check_constraints
information_schema collation_character_set_applicability
information_schema collations
information_schema column_domain_usage
information_schema column_privileges
information_schema column_udt_usage
information_schema columns
information_schema constraint_column_usage
information_schema constraint_table_usage
information_schema data_type_privileges
information_schema domain_constraints
information_schema domain_udt_usage
information_schema domains
information_schema element_types
information_schema enabled_roles
information_schema foreign_data_wrapper_options
information_schema foreign_data_wrappers
information_schema foreign_server_options
information_schema foreign_servers
information_schema foreign_table_options
information_schema foreign_tables
information_schema information_schema_catalog_name
information_schema key_column_usage
information_schema parameters
information_schema referential_constraints
information_schema role_column_grants
information_schema role_routine_grants
information_schema role_table_grants
information_schema role_usage_grants
information_schema routine_privileges
information_schema routines
information_schema schemata
information_schema sequences
information_schema sql_features
information_schema sql_implementation_info
information_schema sql_languages
information_schema sql_packages
information_schema sql_parts
information_schema sql_sizing
information_schema sql_sizing_profiles
information_schema table_constraints
information_schema table_privileges
information_schema tables
information_schema triggered_update_columns
information_schema triggers
information_schema usage_privileges
information_schema user_mapping_options
information_schema user_mappings
information_schema view_column_usage
information_schema view_routine_usage
information_schema view_table_usage
information_schema views

Sumber Referensi

Comments