Datentypen eines Abfrageergebnisses ermitteln

7. August 2013 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

Mit ein wenig Erfahrung und der Kenntnis der Datentypen des SQL Servers und insbesondere der Rangfolge der Datentypen kann man leicht ausmachen, welcher Datentyp bei einer Berechnung von einer Abfrage zurück geliefert wird.

War man sich nicht sicher, konnte man einfach mit einer SELECT ... INTO Anweisung aus dem Abfrageergebnis eine neue Tabelle erstellen lassen und sich dann in deren Definition die Typen ansehen.

Mit dem SQL Server 2012 wurde die neue dynamische Verwaltungsfunktion sys.dm_exec_describe_first_result_set eingeführt, mit dessen Hilfe man zu einer Abfrage die Datentypen selektieren kann.

Einfaches Beispiel mit ein paar Berechnungen sowie festen Werten und einer Funktion:

DECLARE @sql nvarchar(max);
-- Rückgabetypen einer Abfrage mit berechneten / festen Werten. SET @sql =
N'SELECT 10 / 2 AS ResInteger' +
N'      ,10 / 2.0 AS ResNumeric1' +
N'      ,10.0 / 2.00000 AS ResNumeric2' +
N'      ,1.2345E6 AS ResFloat' +
N'      ,''Varchar'' AS ResVarchar' +
N'      ,N''NVarchar'' AS ResNVarchar' +
N'      ,CONVERT(varchar, '''') AS ResDefault' +
N'      ,1/1 AS ResNull' +
N'      ,ISNULL(1/1, 1/1) AS ResNotNull' +
N'      ,ISNULL(null, null) AS ResNullOrNotNull'

SELECT FRS.column_ordinal AS ordinal
      ,FRS.name
      ,FRS.is_nullable
      ,FRS.system_type_name AS systemtype
      ,FRS.max_length AS maxlength
      ,FRS.precision
      ,FRS.scale
      ,FRS.collation_name
FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 0) AS FRS

Das Ergebnis:

Interessant finde ich hier, wann ein berechnetes Feld als Nullable betrachtet wird und wann nicht.

Über den 3ten Parameter @include_browse_information kann man steuern, wie "tief" detailliert das Ergebnis ausfallen soll.
Wenn man z.B. sys.dm_exec_describe_first_result_set auf eine Abfrage auf eine View anwendet und dabei den Parameter mit Wert 0 angibt, erhält man "nur" die Spaltenwerte für das finalle Abfrageergebnis, wie zuvor gesehen.
Übergibt man den Parameter mit Wert = 1, erhält man über die "source_..." Spalten noch die Information, aus welcher Basistabelle die Spalten eigentlich kommen.

Gutes Beispiel ist hier die View vSalesPerson, die Werte aus diversen Tabellen zuück liefert:

DECLARE @sql nvarchar(max);

-- Abfrage auf View mit den Daten zu den Quelltabellen.
SET @sql =
N'SELECT *
FROM [AdventureWorks2012].[Sales].[vSalesPerson]';

SELECT FRS.column_ordinal
      ,FRS.system_type_name
      ,FRS.source_schema
      ,FRS.source_table
      ,FRS.source_column
      ,FRS.name AS finalname
FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 1) AS FRS;

Ergebnis:

Das funktioniert sogar bei Abfragen auf System Views

DECLARE @sql nvarchar(max);

-- Abfrage auf System Views.
SET @sql =
N'SELECT *
  FROM sys.tables AS TBL
       INNER JOIN
       sys.columns AS COL
           ON TBL.object_id = COL.object_id';
SELECT FRS.column_ordinal
      ,FRS.system_type_name
      ,FRS.source_schema
      ,FRS.source_table
      ,FRS.source_column
      ,FRS.name AS finalname
      ,FRS.source_database
FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 1) AS FRS;

hier sieht man sogar, aus welcher Systemtabelle die Daten kommen:

Mit Hilfe der DMF kann man ein Statement überprüfen lassen, ob es syntaktisch Korrekt und ob es im aktuellen Kontext auch fehlerfrei ausgeführt werden. Treten Fehler auf, erhält man die Fehlermeldung im Ergebnis mit der column_ordinal = 0; normalerweise beginnt der Wert mit 1.
Das ist zum Beispiel hilfreich, wenn man SQL Statements von einer Appliaktion dynamisch zusammen mit Benutzereingaben erstellt und vor der Ausführung kontrollieren will, ob Fehler auftreten werden, bevor sie es tun.

Beispiel:

DECLARE @sql nvarchar(max);

-- Abfrage parsen & kompilieren lassen,
-- um evtl. Fehlermeldungen zu erhalten
SET @sql =
N'SELECT UnbekanntesFeld from sys.objects';

SELECT FRS.error_number
      ,FRS.error_severity
      ,FRS.error_state
      ,FRS.error_message
      ,FRS.error_type
      ,FRS.error_type_desc
FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 0) AS FRS
WHERE FRS.column_ordinal = 0;