dtcSoftware - powerShellDba

📌 Warum Spaltennamen auslesen?

MOTIVATION

Jeder Datenbankentwickler und Administrator steht regelmäßig vor der Aufgabe, die Spalten einer Tabelle zu ermitteln – sei es für Dokumentation, dynamische Codegenerierung, Migrationen oder Abfragen. Manuelle Wege (z. B. „Spalten“ im SSMS-Objekt-Explorer) sind umständlich und nicht reproduzierbar. Viel besser ist es, direkt auf die Systemkataloge zuzugreifen. Dieser Artikel zeigt Ihnen verschiedene SQL-Skripte, um Spaltennamen auszulesen, und erklärt, warum der Einsatz automatisiert erfolgen sollte.

Typische Einsatzszenarien:

📄 Dokumentation – Automatisches Erstellen von Datenwörterbüchern oder Excel-Listen aller Tabellen mit Spalten, Datentypen und Constraints.
⚙️ Dynamische SQL-Generierung – Erzeugen von INSERT-, UPDATE- oder SELECT-Statements für unbekannte Tabellen (z. B. in ETL-Prozessen).
🔄 Migration & Schema-Vergleich – Spalten zweier Datenbanken vergleichen, um Unterschiede zu erkennen.
🔧 Tools & Skripte – In administrativen Skripten, die auf jede Tabelle einer Datenbank reagieren sollen.
📊 Reporting & Data Lineage – Herkunft von Daten nachvollziehen, wenn Spaltennamen bekannt sind.

💡 Kernidee: Statt sich auf GUI-Tools zu verlassen, sollte man die Metadaten direkt aus sys.columns, INFORMATION_SCHEMA.COLUMNS oder ähnlichen Sichten abfragen. Das ist schneller, wiederholbar und kann in automatisierte Prozesse eingebettet werden.

📖 Methode 1: INFORMATION_SCHEMA.COLUMNS

STANDARD

Die INFORMATION_SCHEMA.COLUMNS Sicht ist ANSI-SQL-kompatibel und daher besonders portabel. Sie liefert neben dem Spaltennamen auch Datentyp, Länge, NULL-Zulässigkeit usw.

-- Alle Spalten einer bestimmten Tabelle (mit Schema)
SELECT 
  COLUMN_NAME,
  DATA_TYPE,
  CHARACTER_MAXIMUM_LENGTH,
  IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
  AND TABLE_NAME = 'IhreTabelle'
ORDER BY ORDINAL_POSITION;

Vorteile: Einfach, standardisiert, lesbar. Nachteil: Nicht alle SQL Server-spezifischen Eigenschaften (z. B. Spaltentyppräzision bei datetime2) sind enthalten. Für komplexe Metadaten (z. B. Identitätsspalten) muss man auf sys.columns zurückgreifen.

⚙️ Methode 2: Systemprozedur sp_columns

KOMPAKT

Die gespeicherte Systemprozedur sp_columns liefert ebenfalls Spalteninformationen – ähnlich wie INFORMATION_SCHEMA.COLUMNS, aber im Resultsets-Format. Sie kann auch mit verschiedenen Parametern filtern.

-- Spalten für eine Tabelle anzeigen
EXEC sp_columns @table_name = 'IhreTabelle', @table_owner = 'dbo';

-- Nur den Spaltennamen ausgeben (ohne Metadaten)
EXEC sp_columns @table_name = 'IhreTabelle', @column_name = NULL;
-- Ergebnismenge enthält Spalte COLUMN_NAME

⚠️ Einschränkung: Die Prozedur gibt ein umfangreiches Resultset zurück, das sich schlecht in dynamischem SQL weiterverarbeiten lässt. Daher ist sie für reine Namensabfragen weniger praktisch als direkte Katalogabfragen.

🔧 Methode 3: sys.columns (die flexibelste Variante)

POWER USER

Die Katalogansicht sys.columns (zusammen mit sys.tables und sys.schemas) gibt Ihnen vollständige Kontrolle über alle Spalteneigenschaften. Sie ist die erste Wahl für eigene Skripte.

-- Einfache Liste aller Spaltennamen einer Tabelle
SELECT name AS ColumnName
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.IhreTabelle')
ORDER BY column_id;

-- Mit Schema, Tabelle und Datentyp
SELECT 
  SCHEMA_NAME(t.schema_id) AS SchemaName,
  t.name AS TableName,
  c.name AS ColumnName,
  TYPE_NAME(c.user_type_id) AS DataType,
  c.max_length,
  c.is_nullable
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.name = 'IhreTabelle'
ORDER BY t.name, c.column_id;

💡 Besonders nützlich: Sie können leicht Spalten herausfiltern, die bestimmten Kriterien entsprechen (z. B. alle datetime-Spalten, alle Spalten mit einem bestimmten Präfix).

📜 Kommagetrennte Spaltenliste – der Klassiker für dynamisches SQL

CODE-GEN.

Ein häufiger Anwendungsfall: Sie möchten alle Spalten einer Tabelle als kommagetrennte Liste erhalten, um daraus dynamische INSERT-, UPDATE- oder SELECT-Statements zu bauen. Hierfür eignet sich STRING_AGG (ab SQL Server 2017) oder die ältere FOR XML PATH-Methode.

-- Moderne Variante mit STRING_AGG (SQL Server 2017+)
DECLARE @TableName NVARCHAR(255) = 'IhreTabelle';
DECLARE @Columns NVARCHAR(MAX);

SELECT @Columns = STRING_AGG(QUOTENAME(name), ', ') WITHIN GROUP (ORDER BY column_id)
FROM sys.columns
WHERE object_id = OBJECT_ID(@TableName);

PRINT @Columns;

-- Alternative (kompatibel mit älteren Versionen, FOR XML PATH)
DECLARE @ColumnsOld NVARCHAR(MAX) = '';
SELECT @ColumnsOld = STUFF((
  SELECT ', ' + QUOTENAME(name)
  FROM sys.columns
  WHERE object_id = OBJECT_ID(@TableName)
  ORDER BY column_id
  FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

PRINT @ColumnsOld;

💡 Einsatzzweck: Mit einer kommagetrennten Liste können Sie z. B. ein SELECT 'INSERT INTO ... VALUES (...)' für alle Zeilen einer Tabelle generieren oder automatisch eine SELECT-Liste für ein dynamisches Pivot-Statement erzeugen.

🏷️ Spalten inkl. Beschreibungen (Extended Properties)

DOKUMENTATION

Wenn Sie nicht nur die technischen Namen, sondern auch die Benutzerbeschreibungen der Spalten auslesen möchten (z. B. für ein Datenwörterbuch), binden Sie die sys.extended_properties mit ein.

SELECT 
  SCHEMA_NAME(t.schema_id) AS [Schema],
  t.name AS [Tabelle],
  c.name AS [Spalte],
  ep.value AS [Beschreibung]
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
LEFT JOIN sys.extended_properties ep 
  ON ep.major_id = c.object_id 
  AND ep.minor_id = c.column_id
  AND ep.name = 'MS_Description'
WHERE t.name = 'IhreTabelle'
ORDER BY c.column_id;

📌 Tipp für die Praxis: Pflegen Sie erweiterte Eigenschaften in Ihrer Datenbank, dann können Sie mit einem solchen Skript automatisch eine Datenbankdokumentation als HTML oder Excel exportieren.

🗂️ Gesamtübersicht: Alle Tabellen mit Spalten

INVENTAR

Manchmal benötigt man eine vollständige Liste aller Tabellen und ihrer Spalten – etwa für ein Datenbankinventar oder um bestimmte Spaltentypen zu suchen.

SELECT 
  SCHEMA_NAME(t.schema_id) AS SchemaName,
  t.name AS TableName,
  c.name AS ColumnName,
  TYPE_NAME(c.user_type_id) AS DataType,
  c.is_nullable
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE SCHEMA_NAME(t.schema_id) = 'dbo' -- optional
ORDER BY SchemaName, TableName, c.column_id;

💡 Erweiterung: Sie können diese Abfrage mit INFORMATION_SCHEMA.TABLES kombinieren, um auch Views oder Systemtabellen zu berücksichtigen.

🔎 Spalte suchen – Finden Sie jede Tabelle mit einer bestimmten Spalte

ANALYSE

Eine häufige Aufgabe: Sie wissen nur den (Teil-)Namen einer Spalte, möchten aber wissen, in welchen Tabellen sie vorkommt. Das folgende Skript durchsucht alle Tabellen der aktuellen Datenbank.

DECLARE @SearchString NVARCHAR(255) = 'KundenID'; -- Gesuchter Spaltenname

SELECT 
  SCHEMA_NAME(t.schema_id) AS SchemaName,
  t.name AS TableName,
  c.name AS ColumnName
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.name LIKE '%' + @SearchString + '%'
ORDER BY TableName, ColumnName;

⚠️ Performance: Auf sehr großen Datenbanken mit vielen Tabellen kann die Suche mit LIKE etwas dauern. In solchen Fällen sollten Sie den Suchbegriff möglichst präzise halten.

🚀 Praxisbeispiel: Dynamische INSERT-Statements generieren

COOKBOOK

Hier eine vollständige Routine, die aus einer beliebigen Tabelle eine kommagetrennte Spaltenliste erzeugt und daraus ein INSERT-Statement mit Platzhaltern baut – nützlich für automatisierte Skripte.

DECLARE @TableName NVARCHAR(255) = 'dbo.Kunden';
DECLARE @Columns NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);

-- Spaltenliste holen (ohne Identitätsspalten, falls gewünscht)
SELECT @Columns = STRING_AGG(QUOTENAME(c.name), ', ') WITHIN GROUP (ORDER BY c.column_id)
FROM sys.columns c
WHERE c.object_id = OBJECT_ID(@TableName)
  AND c.is_identity = 0; -- Identitätsspalten ausschließen

-- INSERT-Statement bauen
SET @SQL = 'INSERT INTO ' + @TableName + ' (' + @Columns + ') VALUES (';
SELECT @SQL = @SQL + STRING_AGG('?', ', ') WITHIN GROUP (ORDER BY column_id)
FROM sys.columns
WHERE object_id = OBJECT_ID(@TableName) AND is_identity = 0;

SET @SQL = @SQL + ')';

PRINT @SQL;
-- Ausgabe: INSERT INTO dbo.Kunden ([Name], [Ort], [PLZ]) VALUES (?, ?, ?)

💡 Erweiterung: Sie können das Skript leicht anpassen, um auch UPDATE-Statements oder SELECT-Abfragen zu generieren.

📊 Vergleich der Methoden – welche wann?

ENTSCHEIDUNG

Methode	Einsatzgebiet	Vorteil	Nachteil
`INFORMATION_SCHEMA.COLUMNS`	Portable Skripte, einfache Metadaten	Standardisiert, lesbar	Fehlt SQL Server-spezifische Details
`sp_columns`	Schneller Überblick in SSMS	Einfache Ausführung	Unhandliches Resultset für Automatisierung
`sys.columns`	Profis, dynamisches SQL, volle Kontrolle	Alle Eigenschaften, schnell, flexibel	Benötigt Verständnis der Systemkataloge
`STRING_AGG`/`FOR XML PATH`	Kommagetrennte Listen, Codegenerierung	Optimiert für dynamische Abfragen	Etwas komplexere Syntax

✅ Fazit: Automatisieren Sie den Zugriff auf Spaltennamen

RESÜMEE

Egal, ob Sie eine einmalige Dokumentation erstellen, ein dynamisches ETL-Framework bauen oder einfach nur schnell herausfinden möchten, welche Spalten eine Tabelle hat – die SQL Server Systemkataloge bieten Ihnen alle Informationen auf dem Silbertablett. Statt sich auf manuelle GUI-Klicks zu verlassen, sollten Sie die gezeigten Skripte in Ihrer täglichen Arbeit verankern.

Unsere Empfehlungen:

🔹 Für schnelle Einzelfall-Abfragen: Verwenden Sie sys.columns mit einem festen Tabellennamen.
🔹 Für generische Skripte (Codegenerierung): Setzen Sie auf sys.columns + STRING_AGG (oder FOR XML PATH für ältere Versionen).
🔹 Für Datenbank-Dokumentation: Kombinieren Sie sys.columns mit sys.extended_properties, um Beschreibungen auszugeben.
🔹 Für portablen Code (z. B. wenn andere DBMS im Spiel sind): Greifen Sie auf INFORMATION_SCHEMA.COLUMNS zurück.

📌 Weiterführende Idee: Bauen Sie diese Abfragen direkt in Ihre CI/CD-Pipelines ein, um Änderungen am Schema zu erkennen oder automatisch Migrationsskripte zu generieren. Mit ein wenig Kreativität sind den Möglichkeiten keine Grenzen gesetzt.