Nowości w SQL Server 2012 – odczyt struktury zwracanych danych

2012-06-10

W SQL Server 2012 pojawiło się kilka obiektów systemowych pozwalających na uzyskanie szczegółowych informacji odnośnie struktury danych zwracanych przez określone zapytanie, bez konieczności jego uruchamiania. Do tego celu służy procedura sp_describe_first_result_set oraz funkcje sys.dm_exec_describe_first_result_set i sys.dm_exec_describe_first_result_set_for_object. W wyniku ich działania otrzymujemy zestaw danych opisujących pierwszy zbiór zwrócony przez dane zapytanie lub obiekt (procedura, funkcja, widok, itp.). Jeżeli dane polecenie nie zwraca zbioru danych otrzymamy pusty wynik.

W celu prezentacji działania powyższych obiektów stworzyłem tabelę i procedurę składowaną:

create table Test
(
    Id int not null primary key,
    Name varchar(50)
)

create procedure pTest
as
    select * from Test

sp_describe_first_result_set

Procedura sp_describe_first_result_set przyjmuje trzy parametry, z czego dwa ostatnie są opcjonalne. Jako pierwszy parametr przekazywane jest polecenie SQL, dla którego chcemy uzyskać informacje na temat struktury danych wynikowych. Drugi parametr pozwala na określenie argumentów polecenia SQL. Trzeci z parametrów przyjmuje wartości 0, 1 lub 2 i służy do wyboru rodzaju prezentowanych informacji. W wyniku działania procedury otrzymujemy szczegółowy opis struktury pierwszego zbioru danych zwróconych przez podane polecenie SQL. W poszczególnych wierszach znajdują się opisy kolumn zwracanego zbioru. Najważniejsze informacje to:

  • is_hidden – czy kolumna jest ukryta
  • name – nazwa kolumny
  • column_ordinal – pozycja kolumny
  • is_nullable – czy kolumna zezwala na wartości NULL
  • system_type_id – identyfikator typu danych
  • system_type_name – nazwa typu danych
  • max_length – maksymalny rozmiar danych
  • collation_name – collation danych tekstowych w kolumnie

Pełna lista wraz z opisami znajduje się na MSDN.

Przykład użycia:

exec sp_describe_first_result_set N'select * from Test'

Zwrócenie informacji tylko o widocznych kolumnach (domyślnie):

exec sp_describe_first_result_set N'select Name from Test', null, 0

Zwrócenie informacji także o kolumnach niewidocznych (uwzględniona została kolumna Id będąca kluczem głównym tabeli, is_hidden = 1):

exec sp_describe_first_result_set N'select Name from Test', null, 1


W przypadku gdy określone polecenie zwraca więcej niż jeden zbiór danych, udostępnione zostaną informacje tylko o pierwszym z nich:

exec sp_describe_first_result_set N'select Id from Test; select Id, Name from Test'


Jeżeli polecenie w zależności od warunku logicznego zwraca zbiory danych o różnej strukturze wygenerowany zostanie błąd:

exec sp_describe_first_result_set N'if (1=1) select Id from Test else select Name from Test'

sys.dm_exec_describe_first_result_set

Funkcja dm_exec_describe_first_result_set działa w identyczny sposób jak omówiona wcześniej procedura, jednak w jej przypadku podanie wszystkich trzech parametrów jest obowiązkowe:

select * from sys.dm_exec_describe_first_result_set(N'select * from Test', null, 0)

select name, system_type_name
from sys.dm_exec_describe_first_result_set(N'pTest', null, 0)
order by column_ordinal

Istnieje jeszcze jedna różnica w stosunku do procedury sp_describe_first_result_set. Jeżeli przekazane w parametrze polecenie będzie błędne procedura zgłosi wyjątek. W przypadku funkcji wynik zostanie zwrócony, a informacje o błędach zostaną umieszczone w odpowiednich kolumnach:

  • error_number – numer błędu
  • error_severity – poziom błędu
  • error_state – stan błędu
  • error_message – komunikat błędu
  • error_type – typ błędu
  • error_type_desc – opis typu błędu
--Odwołanie do nieistniejącego obiektu Test1
select * from sys.dm_exec_describe_first_result_set(N'select * from Test1', null, 0)

sys.dm_exec_describe_first_result_set_for_object

Funkcja dm_exec_describe_first_result_set_for_object działa analogicznie do powyższej, jednak jako pierwszy parametr przyjmuje ID obiektu procedury składowanej lub triggera (w przypadku innych obiektów zgłoszony zostanie błąd). W jej przypadku nie podaje się parametru z listą argumentów:

select * from sys.dm_exec_describe_first_result_set_for_object(Object_Id('pTest'), 0)

Więcej informacji na ten temat można znaleźć na MSDN: sp_describe_first_result_set
sys.dm_exec_describe_first_result_set
, sys.dm_exec_describe_first_result_set_for_object

Reklamy

Posted on 2012-06-10, in SQL Server and tagged , , , . Bookmark the permalink. 2 komentarze.

  1. Wszystko idzie gładko, jeśli procedura, której wyniku metadane chcemy zobaczyć, jest napisana w T-SQL (CLR i extended odpadają), nie używa tabel tymczasowych, ma wszystkie możliwe wyniki o takiej samej strukturze. Dużo ograniczeń wynikających z algorytmu parsowania obiektów, wszystkie opisane w BOL. Co więcej, implementacja tych obiektów pociągnęła za sobą breaking change (niektórzy rozpatrują to jako bug)… Zobacz tu: http://connect.microsoft.com/SQLServer/feedback/details/737341/sql-server-2012-openrowset-on-msdb-dbo-sp-help-job-throws-error.

    • Niestety nie jest to idealny mechanizm i nie w każdej sytuacji będzie przydatny, ma sporo ograniczeń, o których wspomniałeś. Pozostaje mieć nadzieję, że w kolejnych wersjach SQL Server będzie dopracowywany.

Skomentuj

Wprowadź swoje dane lub kliknij jedną z tych ikon, aby się zalogować:

Logo WordPress.com

Komentujesz korzystając z konta WordPress.com. Log Out / Zmień )

Zdjęcie z Twittera

Komentujesz korzystając z konta Twitter. Log Out / Zmień )

Facebook photo

Komentujesz korzystając z konta Facebook. Log Out / Zmień )

Google+ photo

Komentujesz korzystając z konta Google+. Log Out / Zmień )

Connecting to %s

%d blogerów lubi to: