INFORMATION_SCHEMA vs. sys Tabellen

Einführung

In relationalen Datenbankmanagementsystemen wie SQL Server gibt es zwei Hauptmethoden, um Metadaten über die Datenbank abzurufen: INFORMATION_SCHEMA-Tabellen und sys-Tabellen. Beide Methoden dienen dem gleichen Zweck, bieten jedoch unterschiedliche Vor- und Nachteile. In diesem Artikel werden wir die Unterschiede zwischen diesen beiden Arten von Tabellen detailliert untersuchen und erklären, warum es beide Arten gibt.

INFORMATION_SCHEMA Tabellen

Die INFORMATION_SCHEMA-Tabellen sind Teil des SQL-92 Standards und bieten eine systemunabhängige Methode, um Metadaten abzurufen. Diese Tabellen sind in vielen verschiedenen Datenbanksystemen verfügbar, darunter SQL Server, MySQL und PostgreSQL. Sie bieten eine standardisierte Schnittstelle, um Informationen über Datenbankobjekte wie Tabellen, Spalten, Schemata und Berechtigungen zu erhalten.

Beispiele für INFORMATION_SCHEMA Tabellen:

• INFORMATION_SCHEMA.TABLES
• INFORMATION_SCHEMA.COLUMNS
• INFORMATION_SCHEMA.SCHEMATA
• INFORMATION_SCHEMA.TABLE_CONSTRAINTS

sys Tabellen

Die sys-Tabellen sind spezifisch für SQL Server und bieten eine umfassendere und detailliertere Ansicht der Datenbankmetadaten. Diese Tabellen sind tief in das SQL Server-System integriert und bieten Zugriff auf viele interne Details, die über INFORMATION_SCHEMA nicht zugänglich sind. Sie sind oft schneller und leistungsfähiger als ihre INFORMATION_SCHEMA-Gegenstücke.

Beispiele für sys Tabellen:

• sys.tables
• sys.columns
• sys.schemas
• sys.objects

Warum gibt es beide Arten?

Es gibt mehrere Gründe, warum sowohl INFORMATION_SCHEMA- als auch sys-Tabellen existieren:

1. Standardisierung: INFORMATION_SCHEMA-Tabellen bieten eine standardisierte Methode zur Abfrage von Metadaten, die in verschiedenen Datenbankmanagementsystemen konsistent ist. Dies erleichtert die Portabilität von Anwendungen zwischen verschiedenen Systemen.
2. Leistung und Detailtiefe: sys-Tabellen bieten detailliertere und leistungsfähigere Abfragen, die speziell auf SQL Server zugeschnitten sind. Sie ermöglichen den Zugriff auf interne Systeminformationen, die über INFORMATION_SCHEMA nicht verfügbar sind.
3. Kompatibilität: Die Unterstützung beider Methoden ermöglicht es Entwicklern und Administratoren, je nach ihren spezifischen Anforderungen und Präferenzen zu wählen.

Verfügbarkeit und Nutzung der object_id

Die object_id ist ein eindeutiger Bezeichner für Datenbankobjekte in SQL Server und ist spezifisch für die sys-Tabellen. Sie ist nicht in den INFORMATION_SCHEMA-Tabellen verfügbar. Die object_id wird in SQL Server verwendet, um jedes Objekt wie Tabellen, Sichten, Prozeduren und vieles mehr eindeutig zu identifizieren. Diese ID ist besonders nützlich, um Beziehungen zwischen verschiedenen Metadatenansichten herzustellen und um detaillierte Abfragen über die Struktur und den Zustand der Datenbank durchzuführen.

Beispiel zur Verwendung der object_id in sys-Tabellen:

n dieser Abfrage wird die object_id verwendet, um Tabellen mit ihren Spalten zu verknüpfen. Durch die Verwendung der object_id können Administratoren und Entwickler präzise und effiziente Abfragen erstellen, die auf spezifische Objekte abzielen, und so detaillierte Einblicke in die Struktur und Beziehungen innerhalb der Datenbank erhalten. Dies macht die object_id zu einem unverzichtbaren Werkzeug in der Verwaltung und Optimierung von SQL Server-Datenbanken.

Verwendung von object_id mit INFORMATION_SCHEMA

Obwohl object_id direkt in INFORMATION_SCHEMA-Tabellen nicht verfügbar ist, gibt es Möglichkeiten, diese Information indirekt zu nutzen, indem man die Ergebnisse von INFORMATION_SCHEMA-Abfragen mit sys-Tabellen kombiniert.

Beispielansatz:

1. Abfrage der gewünschten Informationen aus INFORMATION_SCHEMA.
2. Verwendung der Ergebnistabelle als Zwischenergebnis und Verknüpfung mit den sys-Tabellen, um die object_id zu erhalten.

Durch diese Methode kann man die Vorteile von INFORMATION_SCHEMA nutzen und gleichzeitig auf die object_id zugreifen, um detailliertere Informationen zu erhalten.

Vergleich von INFORMATION_SCHEMA und sys Tabellen

INFORMATION_SCHEMA Tabelle	sys Tabelle	Beschreibung
INFORMATION_SCHEMA.TABLES	sys.tables	Informationen über Tabellen in der Datenbank.
INFORMATION_SCHEMA.COLUMNS	sys.columns	Informationen über Spalten in den Tabellen.
INFORMATION_SCHEMA.SCHEMATA	sys.schemas	Informationen über Schemata in der Datenbank.
INFORMATION_SCHEMA.TABLE_CONSTRAINTS	sys.objects	Informationen über Objekte und deren Einschränkungen.
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE	sys.foreign_keys	Informationen über Spalten, die Einschränkungen verwenden.
INFORMATION_SCHEMA.KEY_COLUMN_USAGE	sys.key_constraints	Informationen über Schlüsselspalten.
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS	sys.foreign_keys	Informationen über referentielle Integritätseinschränkungen.
INFORMATION_SCHEMA.CHECK_CONSTRAINTS	sys.check_constraints	Informationen über CHECK-Einschränkungen.
INFORMATION_SCHEMA.ROUTINES	sys.procedures	Informationen über gespeicherte Prozeduren und Funktionen.
INFORMATION_SCHEMA.PARAMETERS	sys.parameters	Informationen über Parameter von Prozeduren und Funktionen.
INFORMATION_SCHEMA.VIEWS	sys.views	Informationen über Sichten in der Datenbank.
INFORMATION_SCHEMA.TRIGGERS	sys.triggers	Informationen über Trigger in der Datenbank.
INFORMATION_SCHEMA.USER_DEFINED_TYPES	sys.types	Informationen über benutzerdefinierte Datentypen.
INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE	Keine direkte Entsprechung	Informationen über Domänen, die von Spalten verwendet werden.
INFORMATION_SCHEMA.DOMAINS	Keine direkte Entsprechung	Informationen über Domänen.
INFORMATION_SCHEMA.USAGE_PRIVILEGES	sys.database_permissions	Informationen über Berechtigungen auf Datenbankobjekte.
INFORMATION_SCHEMA.APPLICABLE_ROLES	sys.database_principals	Informationen über Datenbankrollen.
INFORMATION_SCHEMA.ROLE_TABLE_GRANTS	sys.database_permissions	Informationen über Tabellenberechtigungen, die Rollen gewährt wurden.
INFORMATION_SCHEMA.CHARACTER_SETS	sys.fulltext_catalogs	Informationen über Zeichensätze.
INFORMATION_SCHEMA.COLLATIONS	sys.column_store_segments	Informationen über Kollationen.
INFORMATION_SCHEMA.TRANSLATIONS	Keine direkte Entsprechung	Informationen über Sprachübersetzungen.
Keine direkte Entsprechung	sys.objects	Allgemeine Informationen über alle Datenbankobjekte.
Keine direkte Entsprechung	sys.partitions	Informationen über Partitionen von Tabellen und Indizes.
Keine direkte Entsprechung	sys.allocation_units	Informationen über Zuordnungseinheiten (Speicher).
Keine direkte Entsprechung	sys.stats	Statistiken über Indizes und Spalten.
Keine direkte Entsprechung	sys.dm_db_index_physical_stats	Physische Statistiken über Datenbankindizes.
Keine direkte Entsprechung	sys.dm_exec_requests	Informationen über aktuelle Datenbankabfragen und deren Status.
Keine direkte Entsprechung	sys.dm_exec_sessions	Informationen über aktuelle Datenbanksitzungen.

Verfügbarkeit von INFORMATION_SCHEMA Tabellen in verschiedenen Datenbanksystemen

Die INFORMATION_SCHEMA-Tabellen sind in vielen relationalen Datenbankmanagementsystemen (RDBMS) vorhanden. Hier ist eine detaillierte Analyse der Verfügbarkeit von INFORMATION_SCHEMA-Tabellen in verschiedenen Datenbanksystemen:

INFORMATION_SCHEMA Tabelle	MySQL/MariaDB	Oracle	PostgreSQL	HANA	DB2	Ingres	SQL Anywhere	SAP MaxDB	SQLite
INFORMATION_SCHEMA.TABLES	Ja	Nein	Ja	Ja	Ja	Nein	Ja	Ja	Nein
INFORMATION_SCHEMA.COLUMNS	Ja	Nein	Ja	Ja	Ja	Nein	Ja	Ja	Nein
INFORMATION_SCHEMA.SCHEMATA	Ja	Nein	Ja	Ja	Ja	Nein	Ja	Ja	Nein
INFORMATION_SCHEMA.TABLE_CONSTRAINTS	Ja	Nein	Ja	Ja	Ja	Nein	Ja	Ja	Nein
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE	Ja	Nein	Ja	Ja	Ja	Nein	Ja	Ja	Nein
INFORMATION_SCHEMA.KEY_COLUMN_USAGE	Ja	Nein	Ja	Ja	Ja	Nein	Ja	Ja	Nein
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS	Ja	Nein	Ja	Ja	Ja	Nein	Ja	Ja	Nein
INFORMATION_SCHEMA.CHECK_CONSTRAINTS	Ja	Nein	Ja	Ja	Ja	Nein	Ja	Ja	Nein
INFORMATION_SCHEMA.ROUTINES	Ja	Nein	Ja	Ja	Ja	Nein	Ja	Ja	Nein
INFORMATION_SCHEMA.PARAMETERS	Ja	Nein	Ja	Ja	Ja	Nein	Ja	Ja	Nein
INFORMATION_SCHEMA.VIEWS	Ja	Nein	Ja	Ja	Ja	Nein	Ja	Ja	Nein
INFORMATION_SCHEMA.TRIGGERS	Ja	Nein	Ja	Ja	Ja	Nein	Ja	Ja	Nein
INFORMATION_SCHEMA.USER_DEFINED_TYPES	Nein	Nein	Ja	Ja	Ja	Nein	Ja	Ja	Nein
INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE	Nein	Nein	Ja	Nein	Ja	Nein	Nein	Nein	Nein
INFORMATION_SCHEMA.DOMAINS	Nein	Nein	Ja	Nein	Ja	Nein	Nein	Nein	Nein
INFORMATION_SCHEMA.USAGE_PRIVILEGES	Ja	Nein	Ja	Ja	Ja	Nein	Ja	Ja	Nein
INFORMATION_SCHEMA.APPLICABLE_ROLES	Ja	Nein	Ja	Nein	Ja	Nein	Nein	Nein	Nein
INFORMATION_SCHEMA.ROLE_TABLE_GRANTS	Ja	Nein	Ja	Nein	Ja	Nein	Nein	Nein	Nein
INFORMATION_SCHEMA.CHARACTER_SETS	Ja	Nein	Ja	Nein	Ja	Nein	Nein	Nein	Nein
INFORMATION_SCHEMA.COLLATIONS	Ja	Nein	Ja	Ja	Ja	Nein	Nein	Nein	Nein
INFORMATION_SCHEMA.TRANSLATIONS	Nein	Nein	Nein	Nein	Nein	Nein	Nein	Nein	Nein

Analyse der Verfügbarkeit

• MySQL/MariaDB: Diese Systeme unterstützen umfassend INFORMATION_SCHEMA-Tabellen, was sie sehr nützlich für plattformübergreifende Anwendungen macht.
• Oracle: Oracle bietet keine INFORMATION_SCHEMA-Tabellen an. Stattdessen verwendet Oracle eigene Data Dictionary Views, wie ALL_TABLES, ALL_TAB_COLUMNS, etc.
• PostgreSQL: PostgreSQL unterstützt die meisten INFORMATION_SCHEMA-Tabellen und bietet somit eine standardisierte Methode zur Metadatenabfrage.
• SAP HANA: HANA unterstützt eine breite Palette von INFORMATION_SCHEMA-Tabellen, obwohl einige spezielle Funktionen fehlen.
• IBM DB2: DB2 bietet Unterstützung für viele INFORMATION_SCHEMA-Tabellen und ergänzt diese durch eigene spezifische Systemtabellen.
• Ingres: Ingres unterstützt keine INFORMATION_SCHEMA-Tabellen und verwendet stattdessen proprietäre Systemtabellen.
• SQL Anywhere: SQL Anywhere unterstützt die meisten INFORMATION_SCHEMA-Tabellen, was die Metadatenabfrage erleichtert.
• SAP MaxDB: SAP MaxDB bietet Unterstützung für die meisten INFORMATION_SCHEMA-Tabellen und ermöglicht so eine standardisierte Metadatenabfrage.
• SQLite: SQLite unterstützt keine INFORMATION_SCHEMA-Tabellen, da es ein leichtgewichtiges und eingebettetes Datenbanksystem ist, das eine einfachere interne Struktur hat.

Die Unterstützung von INFORMATION_SCHEMA-Tabellen variiert je nach Datenbanksystem. Während Systeme wie MySQL, PostgreSQL und SQL Anywhere umfassende Unterstützung bieten, haben andere Systeme wie Oracle und Ingres keine native Unterstützung und verwenden stattdessen proprietäre Mechanismen zur Metadatenabfrage. Das Verständnis dieser Unterschiede ist wichtig für die Portabilität von Anwendungen und die Entwicklung plattformübergreifender Datenbanklösungen.

Fazit

INFORMATION_SCHEMA- und sys-Tabellen bieten unterschiedliche Ansätze zur Abfrage von Metadaten in SQL Server. Während INFORMATION_SCHEMA-Tabellen eine standardisierte und systemunabhängige Methode bieten, ermöglichen sys-Tabellen tiefere Einblicke und bessere Leistung für SQL Server-spezifische Anforderungen. Beide Methoden haben ihre eigenen Vor- und Nachteile, und die Wahl zwischen ihnen hängt von den spezifischen Anforderungen und Präferenzen des Entwicklers oder Administrators ab.

Zusammengefasst:

• INFORMATION_SCHEMA Tabellen: Einfachere Abfragen, weniger Joins, standardisierte und plattformunabhängige Metadaten.
• sys Tabellen: Umfangreichere und detailliertere Informationen, komplexere Abfragen mit mehreren Joins, SQL Server-spezifische Metadaten.

Die object_id ist ein Merkmal der sys-Tabellen und nicht in den INFORMATION_SCHEMA-Tabellen verfügbar. Dies ermöglicht in sys-Tabellen tiefere und detailliertere Abfragen, die für fortgeschrittene Datenbankverwaltungsaufgaben erforderlich sind. Durch die Kombination von INFORMATION_SCHEMA- und sys-Tabellen können Administratoren und Entwickler das Beste aus beiden Welten nutzen, um umfassende und präzise Metadatenabfragen durchzuführen.