[PL][util] Piszemy narzędzia w T-SQL cz. 2 - sp_findobjects
Słowem wstępu
Notką zatytułowaną "Piszemy narzędzia w T-SQL cz. 1 - sp_getcolumns" rozpocząłem ponad miesiąc temu serię poświęconą pisaniu użytecznych procedur, które mogą zostać wykorzystane jako swego rodzaju rozszerzenia narzędzi typu Management Studio. Tym razem celem notki będzie zilustrowanie procesu tworzenia procedury do wyszukiwania obiektów w bieżącej bazie danych.
Piszemy utilsa sp_findobjects
Bardzo często zdarza nam się, że wchodzimy na "nieznany teren", czyli mamy do czynienia z nieznaną nam bazą danych lub też znamy bazę, ale ma ona w sobie tyle obiektów, że wszystkich zapamiętać nie jesteśmy w stanie (np. baza, na której pracuję codziennie, ma ponad 90 tysięcy obiektów). Jeżeli przyjdzie nam szukać w tym gąszczu obiektów, to oczywiście, możemy użyć do tego celu narzędzi graficznych - filtrów w Management Studio (prawy klik na folderze Tables w oknie Object Explorer i wybieramy opcję Filter - Filter Settings...). Problem z takimi narzędziami polega na tym, że za każdym razem, gdy chcemy zmienić wartości pól filtra, musimy wykonać szereg czynności za pomocą myszy (lub klawiatury, ale wówczas będzie jeszcze trudniej).
Jeżeli jednak jesteśmy maniakami kodu i/lub każde dotknięcie myszy czy touchpada wywołuje na naszych twarzach grymas bólu, to możemy napisać sobie utilsa do wyszukiwania obiektów w bazach danych.
Do dzieła więc. Przypominam, że głównym pomysłem, na którym oparte są moje utilsy, jest używanie prefiksu sp_ oraz tworzenie tych procedur w bazie master (dzięki czemu są one widoczne także z poziomu każdej innej bazy). Procedura sp_findobjects będzie miała w moim przypadku trzy parametry: @name - do którego podawać będziemy fragment nazwy szukanego obiektu, @pattern - szablon podstawiany w kodzie procedury do warunku wyszukiwania (to nam umożliwi np. zapytywanie o obiekty z podanym ciągiem znaków na początku nazwy zamiast w dowolnym miejscu, jak to ma domyślnie miejsce - patrz wartość domyślna), @constraints - ten parametr pozwoli wywołującemu procedurę decydować, czy w wyszukiwaniu mają być uwzględniane obiekty typu constraints (klucze główne, obce etc.).
CREATE PROC dbo.sp_findobjects
@name sysname,
@pattern nvarchar(300) = N'%$object%',
@constraints bit = 0
AS
W tym miejscu czas na "ciało" procedury. Standardowo pierwszą linijką procedury jest u mnie:
SET NOCOUNT ON
Czas na właściwy kawałek kodu:
SELECT QUOTENAME(schema_name([schema_id])) + N'.' + QUOTENAME([name])
FROM sys.all_objects WITH (NOLOCK)
WHERE QUOTENAME(schema_name([schema_id])) + N'.' + QUOTENAME([name]) LIKE REPLACE(@pattern, '$object', @name)
AND (@constraints = 1 OR [type] NOT IN ('C','D','F','PK','UQ'))
ORDER BY QUOTENAME(schema_name([schema_id])) + N'.' + QUOTENAME([name])
Powyższe polecenie SELECT wybiera z widoku systemowego sys.all_objects (w bieżącej bazie danych) nazwę obiektów (w formacie [nazwa_schematu].[nazwa_obiektu]), które spełniają określone warunki:
- nazwa obiektu złożona z dwóch członów - [nazwa_schematu].[nazwa_obiektu] - zawiera podany w parametrze @name ciąg znaków (wartość @name jest podstawiana w miejsce znacznika $object w łańcuchu podanym w parametrze @pattern, dzięki czemu mamy możliwość sterowania, w którym miejscu nazwy nastąpi wyszukanie),
- jeżeli @constraints = 0, to obiekt nie może być typu constraint ([type] NOT IN ('C','D','F','PK','UQ')).
Tym sposobem pierwsza wersja utilsa sp_findobjects jest gotowa (cały kod dostępny w załączniku). Można utilsa podłączać pod skrót klawiaturowy w Management Studio i testować.
Testujemy utilsa
Po podłączeniu utilsa pod skrót klawiaturowy czas na test. W pustym oknie skryptu w Management Studio zmieniamy aktualną bazę na msdb i wpisujemy:
job
Zaznaczamy wpisany tekst i wciskamy odpowiednią kombinację klawiszy (pod którą wcześniej podłączyliśmy utilsa) i dowiadujemy się, jakie obiekty w bazie msdb dotyczą bezpośrednio jobów.
Co dalej?
Ten util może być bardzo łatwo rozwinięty o kilka dotatkowych funkcjonalności, np.:
- wyszukiwanie według typów obiektów (czyli np. tylko tabele, tylko procedury, etc.),
- wyszukiwanie także obiektów nie znajdujących się w schematach (np. triggery DDL),
- wyszukiwanie we wszystkich bazach naraz.
Odnośnie przeszukiwania wszystkich baz, można wypróbować też add-in DB Object QuickFind dla Management Studio autorstwa Josepha Cooney'a.