SQL Server 2012 - Nowe możliwości odczytu metadanych  

Autor: Damian Widera

Opublikowano: 2012-12-12

SQL Server udostępniał zawsze informacje na temat metadanych odnoszących się do obiektów zawartych w bazie danych. Dotyczy to zarówno tabel, widoków, jak i np. procedur składowanych czy funkcji. Informacje te były dostępne po wykonaniu kwerendy na tabelach systemowych, co samo w sobie nie jest dobrym sposobem. Drugą metodą jest możliwość skorzystania z widoków informacyjnych, tzw. INFORMATION_SCHEMA.

Dużo trudniejszym zadaniem jest odczytanie metadanych, zwracanych przez procedurę składowaną, która zawiera skomplikowaną logikę biznesową, np. posiada wiele rozgałęzień w kodzie. Zazwyczaj w takim wypadku programiści korzystali z opcji SET FMTONLY ON, która pozwalała na zwrócenie informacji o metadanych bez wykonania zapytania:

USE AdventureWorks2012
GO
SET FMTONLY ON
SELECT * FROM HumanResources.Employee;
SET FMTONLY OFF

W powyższym przykładzie zapytanie nie zwróci wierszy z tabeli HumanResources.Employee, a jedynie informacje o kolumnach.

W SQL Server 2012 ułatwiono, w znaczny sposób, programistom pracę z metadanymi, wprowadzając kilka nowych systemowych procedur składowanych oraz funkcji. Obiekty te, to:

• sys.sp_describe_first_result_set,
• sys.dm_exec_describe_first_result_set,
• sys.dm_exec_describe_first_result_set_for_object,
• sys.sp_describe_undeclared_parameters.

Procedura sys.sp_describe_first_result_set przyjmuje jako parameter wyrażenie TSQL i jako wynik zwraca bardzo dokładne informacje dotyczące metadanych wyrażenia. Na poniższym przykładzie pokazano działanie tej procedury:

EXEC sp_describe_first_result_set  @tsql = N'SELECT * FROM HumanResources.Employee'

Poniżej przedstawiony został fragment informacji, zwracanych przez procedurę dla zapytania, jak powyżej:

Funkcja sys.dm_exec_describe_first_result_set działa bardzo podobnie do systemowej procedury składowanej sys.sp_describe_first_result_set, opisanej powyżej. Użycie funkcji pozwala na łatwiejsze filtrowanie wyników, np. w podanym przykładzie wyświetlone zostaną tylko te kolumny wynikowe, których atrybut wynosi Is_Nullable=1:

SELECT *
FROM sys.dm_exec_describe_first_result_set
('SELECT * FROM HumanResources.Employee', NULL, 1)
WHERE is_nullable = 1

Poniżej zaprezentowany został przykład, w którym pokazano, w jaki sposób użyć parametryzacji zapytania:

SELECT name, system_type_name
FROM sys.dm_exec_describe_first_result_set
('
        IF @SortOrder = 1
            SELECT OrderDate, TotalDue
            FROM Sales.SalesOrderHeader
            ORDER BY SalesOrderID ASC
        ELSE IF @SortOrder = 0
            SELECT OrderDate, TotalDue
            FROM Sales.SalesOrderHeader
            ORDER BY SalesOrderID DESC',
        '@SortOrder AS tinyint', 1
)

Jeśli parametr @SortOrder zostanie ustawiony na wartość 1 (jak powyżej), to funkcja zwróci następujący wynik:

Jeśli jednak wywołamy to zapytanie, zmieniając wartość parametru @SortOrder na 0, to otrzymamy wynik nieco inny:

SELECT name, system_type_name
FROM sys.dm_exec_describe_first_result_set
('
        IF @SortOrder = 1
            SELECT OrderDate, TotalDue
            FROM Sales.SalesOrderHeader
            ORDER BY SalesOrderID ASC
        ELSE IF @SortOrder = 0
            SELECT OrderDate, TotalDue
            FROM Sales.SalesOrderHeader
            ORDER BY SalesOrderID DESC',
        '@SortOrder AS tinyint', 0
)

Nie należy sugerować się tym, że w jednym z wywołań uzyskaliśmy kolumnę SalesOrderID. Ta kolumna nie jest zwracana do aplikacji klienckiej (nie jest na liście SELECT), a służy do przeszukiwania wyników zapytania. Najłatwiej potwierdzić to, sprawdzając jej własność Is_Hidden.

Należy pamiętać, że wywołanie tej procedury zakończy się błędem, jeśli SQL Server stwierdzi, że schematy nie są kompatybilne. Taka sytuacja ma miejsce, jeśli w wyniku zastosowania logiki zapytania otrzymujemy różne zestawy kolumn, jak pokazano poniżej:

EXEC sys.sp_describe_first_result_set
    @tsql = N'
    IF @IncludeCurrencyRate = 1
    SELECT OrderDate, TotalDue, CurrencyRateID
    FROM Sales.SalesOrderHeader
    ELSE
    SELECT OrderDate, TotalDue
    FROM Sales.SalesOrderHeader'

Po uruchomieniu zapytania otrzymujemy błąd:

Msg 11509, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because the statement 'SELECT OrderDate, TotalDue, CurrencyRateID
FROM Sales.SalesOrderHeader' is not compatible with the statement 'SELECT OrderDate, TotalDue
FROM Sales.SalesOrderHeader'.

Jeśli zamiast procedury sys.sp_describe_first_result_set użyta zostanie funkcja sys.dm_exec_describe_result_set, to wywołanie nie zakończy się błędem, a zostanie zwrócony wynik w postaci NULL.

Funkcja sys.dm_exec_describe_first_result_set_for_object pozwala uzyskać powyższe informacje dla obiektów bazodanowych, ponieważ przyjmuje jako parametr identyfikator (Object_ID) obiektu:

SELECT name, system_type_name, is_hidden
FROM sys.dm_exec_describe_first_result_set_for_object
(
OBJECT_ID('[dbo].[uspGetBillOfMaterials]'), 1
)

Procedura składowana sys.sp_describe_undeclared_parameters parsuje podane wyrażenie TSQL i próbuje odgadnąć informacje o typach parametrów. Poniżej przedstawiony został przykład prezentujący jej działanie:

EXEC sys.sp_describe_undeclared_parameters N'IF @IsFlag = 1 SELECT 1 ELSE SELECT 0'

Po jej uruchomieniu uzyskujemy informacje o parametrze @IsFlag (przedstawiono fragment informacji). SQL Server zidentyfikował ten parametr jako integer, na podstawie kontekstu zapytania TSQL:

EXEC sys.sp_describe_undeclared_parameters N'IF @IsFlag = 1 SELECT 1 ELSE SELECT 0'

Na zakończenie warto pamiętać, że póki co, za pomocą omówionych procedur składowanych, nie da się odczytać metadanych procedur, wykonanych w CLR, a także tzw. extended SP.