SQLGEEK

[PL][util] Piszemy narzędzia w T-SQL cz. 1 - sp_getcolumns

Słowem wstępu

Jakiś czas temu nagrałem dla Technet Polska screencast zatytułowany SQL Server Management Studio - użycie własnych procedur jako narzędzi. Screencast ten zapewne "utonął" w natłoku informacji, jakie przewijają się przez strony Technetu. W nagraniu była mowa o tym, jak można w łatwy sposób wykorzystać procedury składowane jako narzędzia codziennej pracy w środowisku SQL Server Management Studio dla SQL Server 2005/2008 oraz w aplikacji Query Analyzer dla SQL Server 2000. Generalnie rozchodzi się o to, by wykorzystać możliwość podłączania wywołania procedur składowanych (ale także i kodu T-SQL ad-hoc) do skrótów klawiaturowych w wymienionych aplikacjach.

Od pewnego czasu myślałem też o tym, żeby na blogu lub na stronie zacząć umieszczać kawałki kodu T-SQL. Nawet zalążki są na mojej stronie. Tyle, że blog daje większe możliwości rozpisania się na temat genezy danego utilsa (tak potocznie nazywam - i nie tylko ja - procedury narzędziowe) i lepszego opisania jego funkcjonowania.

Stąd ten wpis rozpoczyna na moim blogu serię pt. "Piszemy narzędzia w T-SQL". Będę od czasu do czasu pisał o tym, jak pisać skrypty, procedury i inne kawałki kodu T-SQL, które mogą okazać się pomocne w codziennej pracy programisty i/lub administratora SQL Server.

Piszemy utilsa sp_getcolumns

Jak często zdarza się nam, że chcemy rozwinąć gwiazdkę w zapytaniu SELECT do pełnej listy kolumn w tabeli lub widoku? Jak często pojawia się potrzeba wypisania lub skopiowania listy parametrów procedury składowanej? Czemu nie napisać sobie prostego automatu, który będzie dla nas takie listy kolumn i parametrów zwracał? 

Zadaniem procedury sp_getcolumns, której podstawowa wersja znajduje się w załączniku do tej notki, jest właśnie listowanie kolumn lub parametrów wybranego obiektu z bieżącej bazy danych. Tak, tak, można napisać takiego utilsa, który będzie się wykonywał w kontekście bieżącej bazy i będzie działał w każdej bazie danych na instancji SQL Server. Tak właśnie działają procedury systemowe. Cała sztuka sprowadza się do umieszczenia procedury w bazie master i do nadania procedurze odpowiedniej nazwy - nazwy zaczynającej się od prefiksu sp_. Drucik? Możliwe, ale jaki prosty i jakie daje możliwości :-)

Zacznijmy więc pisanie. Procedura jak to procedura, powinna mieć sygnaturę. W tym przypadku wymyśliłem sobie, że sp_getcolumns v1.0 będzie przyjmowała dwa parametry - nazwę obiektu oraz flagę, dzięki której będę sterował sposobem wyświetlania wyniku (zwracana lista w postacie jednego wiersza - @horizontal = 1 lub w postaci wielu wierszy @horizontal = 0). A zatem początek wygląda tak:

CREATE PROC dbo.sp_getcolumns
@object sysname,
@horizontal tinyint = 0
AS

W tym miejscu czas na "ciało" procedury. Standardowo pierwszą linijką procedury jest u mnie:

SET NOCOUNT ON

Dzięki powyższej linii kodu SQL Server nie będzie z wnętrza mojej procedury zwracał komunikatów informujących o ilości wstawionych, zmienionych, usuniętych czy wybranych wierszy. Czyli takie swego rodzaju odśmiecanie zostało włączone.

Wynikiem działania naszej procedury będzie zwrócenie listy. Do przechowywania i budowania tej listy potrzebna mi jest tabela. Nie lubię tabel tymczasowych (z prefiksem #). Oczywiście, czasem mają swoje dobre strony, co pokazał Maciek Pilecki na swojej sesji o mitach na konferencji C2C'08. W tym jednak przypadku danych nie będzie wiele i tworzenie tabeli tymczasowej zamiast zmiennej tabelarycznej nie ma, moim zdaniem, sensu. Stąd:

DECLARE @lines TABLE (line_id int identity(1,1), line nvarchar(max))

Ktoś mógłby powiedzieć, że użycie nvarchar(max) to zły pomysł. Z punktu widzenia wydajności pewnie tak. Ale jeżeli wziąć pod uwagę, że mogę mieć tabelę z ponad tysiącem kolumn, z których każda może mieć nazwę o długości do 128 znaków, to maksymalna ilość znaków w tradycyjnych typach varchar/nvarchar (odpowiednio 8000 i 4000 znaków) może okazać się zbyt mała do przechowania listy w jednym wierszu. A tak, gigabajt lub dwa gigabajty tekstu się zmieszczą...

Teraz następuje już właściwa część procedury. Na początek sprawdzenie, czy w ogóle jest o co walczyć, czyli czy dany obiekt ma kolumny lub parametry:

IF EXISTS (SELECT 1 FROM sys.columns WHERE [object_id] = OBJECT_ID(@object))
BEGIN

Następnie sprawdzenie, czy kolumny/parametry obiektu mają być listowane jako jeden wiersz, czy też każda kolumna/parametr ma się znaleźć w osobnym wierszu (to się może przydać, jeśli chcemy na przykład porządnie formatować kod T-SQL):

  IF @horizontal = 1 BEGIN
    DECLARE @line nvarchar(max)
    SET @line = N''
    SELECT @line = @line + [name] + N', '
    FROM sys.columns WITH (NOLOCK)
    WHERE [object_id] = OBJECT_ID(@object)
    ORDER BY column_id
    INSERT @lines (line)
    SELECT LEFT(@line,LEN(@line)-1)
  END
  ELSE BEGIN
    INSERT @lines (line)
    SELECT [name] + N','
    FROM sys.columns WITH (NOLOCK)
    WHERE [object_id] = OBJECT_ID(@object)
    ORDER BY column_id
    UPDATE @lines
    SET line = LEFT(line,LEN(line)-1)
    WHERE line_id = @@IDENTITY
  END
END

Dzięki prostym zapytaniom do widoku systemowego sys.columns oraz chwytowi z doklejaniem kolejnych wierszy z wyniku zapytania do zmiennej @line dostaję listę kolumn/parametrów obiektu w formie zależnej od wartości parametru @horizontal. Operacje z wykorzystaniem funkcji LEN to czysta formalność - wycinam w ten sposób ostatni przecinek.

Na koniec nie pozostaje nic innego, jak zwrócić uporządkowaną listę kolumn/parametrów:

SELECT line FROM @lines ORDER BY line_id

Tym sposobem sp_getcolumns v1.0 jest gotowa (cały kod wraz z testami jest dostępny w załączniku, do którego link znajduje się na końcu tej notki) i można ją podłączać pod skrót klawiaturowy w Management Studio i testować.

Podłączamy utilsa pod skrót klawiaturowy w Management Studio

Podłączenie procedury pod skrót odbywa się w Management Studio przez wejście w menu główne Tools - Options..., a następnie w oknie Options wejście w Environment - Keyboard w drzewie po lewej stronie okna. Nazwę procedury wpisujemy obok wybranego przez nas skrótu i... niestety po kliknięciu OK musimy zrestartować Management Studio, żeby cieszyć się nową funkcjonalnością.

Testujemy utilsa

Utilsa przetestujemy na obiektach bazy AdventureWorks, którą większość osób używających SQL Server 2005 wykorzystuje jako swego rodzaju poligon doświadczalny. W Management Studio otwieramy nowe okno edytora skryptów T-SQL, zmieniamy bieżącą bazę na AdventureWorks i wpisujemy nazwę interesującego nas obiektu, np. 'Production.Product' (nazwa złożona z dwóch lub więcej członów musi być ujęta w apostrofy, by procedura sp_getcolumns wykonała się poprawnie). Zaznaczamy nazwę obiektu (razem z apostrofami) i wciskamy skrót klawiaturowy wybrany wcześniej dla procedury sp_getcolumns. W wyniku powinniśmy otrzymać listę kolumn tabeli, przy czym każda kolumna będzie wyświetlona w osobnym wierszu. Jeżeli zależy nam na liście w jednym wierszu, dopisujemy przecinek i jedynkę:

'Production.Product', 1

zaznaczamy cały powyższy kawałek kodu i ponownie używamy skrótu klawiaturowego.

Ważne, by zawsze znajdować się w kontekście właściwej bazy przed użyciem utilsa sp_getcolumns.

Co dalej?

Możliwości rozbududowy utilsa opisanego powyżej są spore. Można dołożyć na przykład obsługę wyświetlania typów danych dla kolumn i parametrów. Można dać możliwość tworzenia wzorców (np. do każdej nazwy kolumny util może dokładać prefiks @, dzięki czemu automatycznie otrzymamy listę parametrów dla nowo tworzonej procedury składowanej). Kwestia inwencji twórczej i potrzeb.

Dlaczego utilsy rządzą?

Odpowiedź jest prosta. Bo dzięki nim możemy wszystko wykonać szybciej, lepiej (dobrze przetestowane automaty nie popełniają błędów) i nie używając myszy :-) Stworzenie biblioteki utilsów nie przychodzi od razu. To lata ciężkiej dłubaniny. Ale tworzenie takich kawałków kodu, dzięki którym w przyszłości mniej się narobimy, daje wielką satysfakcję. Polecam.

PS. Jeżeli napisałeś ciekawego utilsa i chcesz się nim pochwalić - wyślij do mnie maila na adres pawel.[_no_spam_]potasinski@sqlpass.org.

[EDIT] Wyciąłem z notki błędną informację, jakoby widoki systemowe w SQL Server 2005 były przeglądane z poziomu bazy, w któej znajduje się procedura. To stwierdzenie okazało się nieprawdziwe. W związku z tym kod utilsa sp_getcolumns też uległ modyfikacji. [/EDIT]