Ćwiczenia > Procedury składowane w MS SQL Server 2000 > Procedury systemowe

8.2 Procedury systemowe



Procedury systemowe to procedury zdefiniowane w serwerze, o których informacje przechowywane są w bazie danych master. Łatwo je poznać po przedrostku sp_. Nie będziemy tu omawiać szczegółowo procedur systemowych, ponieważ jest ich zbyt wiele, a poza tym ich szczegółowy opis znajdziesz w Books Online. Niektóre z procedur wykorzystujemy w innych lekcjach podręcznika, o innych możesz poczytać samemu.

Krok 1 - Zobacz listę procedur systemowych

1. Uruchom program Query Analyzer.
2. Zaloguj się używając uwierzytelnienia systemu Windows.
3. W menu głównym programu wybierz File - Open.
4. W oknie Open Query File wybierz plik demo_8_2_1.sql.
5. Zaznacz i uruchom (klawisz F5) fragment kodu oznaczony w komentarzu jako (1).


-- (1) nazwy procedur systemowych
SELECT name
FROM master..sysobjects
WHERE name LIKE 'sp_%' AND Xtype = 'P'
ORDER BY NAME
GO


Wynikiem wykonania powyższego kodu jest wyświetlenie z bazy danych master nazw procedur systemowych.

6. Wciśnij klawisz F1 pracując w programie Query Analyzer. W Books Online przejdź do zakładki Indeks po lewej stronie okna i wpisz w pole tekstowe sp. Wyświetlona zostanie lista systemowych procedur składowanych. Możesz w ten sposób przejrzeć ich listę i uzyskać ich dokładny opis.

Krok 2 - Zobacz definicję przykładowej procedury systemowej

1. Zaznacz i uruchom (klawisz F5) fragment kodu oznaczony w komentarzu jako (2).


-- (2) definicje procedur systemowych
SELECT ob.name, comm.text
FROM master..sysobjects ob
INNER JOIN master..syscomments comm
ON ob.id = comm.id 
WHERE ob.name = 'sp_help'
GO


Wynikiem wykonania powyższego kodu jest uzyskanie kodu definicji procedury systemowej sp_help, jednej z podstawowych procedur systemowych (zwraca ona informacje o obiektach baz danych).

Oczywiście kod definicji procedury systemowej jest dla Ciebie mniej istotny od dokładnego opisu jej działania. W jaki sposób możesz dostać się do opisu procedury - o tym czytaj poniżej.

Krok 3 - Jak uzyskać pomoc na temat procedury systemowej

1. We fragmencie kodu oznaczonym w komentarzu jako (2) zaznacz nazwę procedury systemowej sp_help i wciśnij kombinację klawiszy Shift+F1. Zostanie otwarte okno pomocy Books Online na stronie poświęconej wybranej procedurze (lub zobaczysz listę możliwych tematów do wyboru, jeśli opis procedury okaże się obszerniejszy).

Praktyka uczy, że nawet najlepiej znający SQL Server specjaliści nie pracują bez Books Online. Wszystkiego po prostu nie sposób pamiętać. Tym bardziej, gdy jesteś początkującym użytkownikiem serwera. Pomoc zawiera oprócz rzetelnych opisów także przykłady zastosowań opisywanych poleceń.

Krok 4 - Przykładowe procedury systemowe i ich działanie

1. Zaznacz i uruchom (klawisz F5) fragment kodu oznaczony w komentarzu jako (3).


-- (3) przyklady uzycia procedur systemowych
USE Northwind
EXEC sp_helpdb Northwind
EXEC sp_help Employees
GO


Wynikiem wykonania powyższego kodu jest wywołanie dwóch procedur systemowych: sp_helpdb zwracającej informacje o bazie danych (tu o bazie Northwind) oraz sp_help zwracającej informacje o wybranym obiekcie bazy danych (tu tabela Employees z bazy danych Northwind).

O ile pamiętasz, pisaliśmy już o użyciu słowa EXEC w rozdziale poświęconym wsadom. Pamiętaj, że najlepiej jest zawsze używać tego słowa przy wywołaniu procedury składowanej. Podanie samej nazwy procedury wraz z parametrami zakończy się zwróceniem błędu przez serwer, jeżeli wywołanie procedury nie jest pierwszym poleceniem we wsadzie.


Krok 4 - Zapoznaj się z wybranymi procedurami systemowymi

Poniżej prezentujemy najczęściej wykorzystywane procedury systemowe, na które powinieneś zwrócić szczególną uwagę.

Procedury dotyczące bezpieczeństwa:

sp_addapprole - dodaje do bazy danych specjalną rolę (grupę) do pracy z aplikacjami,
sp_addlogin - dodaje w serwerze nowy login MS SQL Servera,
sp_addrole - dodaje nową grupę do bazy danych,
sp_addrolemember - dodaje do grupy w bazie danych istniejącego w bazie użytkownika,
sp_addsrvrolemember - dodaje do grupy serwera istniejącego użytkownika serwera (login SQL Server lub użytkownika / grupy systemu Windows),
sp_approlepassword - umożliwia zmianę hasła dla roli do pracy z aplikacjami,
sp_changedbowner - zmienia właściciela bazy danych,
sp_changeobjectowner - zmienia właściciela wybranego obiektu bazy danych,
sp_dbfixedrolepermission - wyświetla uprawnienia grupy w bazie danych (ale nie grupy stworzonej przez użytkownika),
sp_defaultdb - zmienia macierzystą bazę danych użytkownika serwera,
sp_denylogin - zabrania dostępu do serwera użytkownikowi lub grupie użytkowników systemu Windows,
sp_dropapprole - usuwa z bazy danych rolę do pracy z aplikacjami,
sp_droplogin - usuwa login SQL Servera o wybranej nazwie, zabrania użytkownikowi o tym loginie dostępu do serwera,
sp_droprole - usuwa z bazy danych rolę stworzoną wcześniej przez użytkownika,
sp_droprolemember - usuwa z grupy w bazie danych wybranego użytkownika,
sp_dropsrvrolemember - usuwa z grupy serwera wybrany login,
sp_grantdbaccess - dodaje użytkownika serwera do bazy danych, umożliwia użytkownikowi korzystanie z bazy danych,
sp_grantlogin - umożliwia użytkownikowi / grupie użytkowników systemu Windows dostęp do serwera,
sp_helprole - wyświetla informacje o wybranej grupie w bieżącej bazie danych,
sp_helprolemember - wyświetla informacje o członkach wybranej grupy w bieżącej bazie danych,
sp_helpsrvrole - wyświetla informacje o wybranej grupie serwera,
sp_helpsrvrolemember - wyświetla informacje o członkach wybranej grupy serwera,
sp_password - umożliwia zmianę hasła dla logina SQL Servera,
sp_revokedbaccess - usuwa użytkownika z bazy danych, uniemożliwia użytkownikowi korzystanie z bazy danych,
sp_revokelogin - usuwa użytkownika / grupę użytkowników systemu Windows z serwera,
sp_setapprole - aktywuje uprawnienia roli do pracy z aplikacjami w bazie danych,
sp_srvrolepermission - wyświetla uprawnienia grupy w serwerze

Procedury dotyczące obsługi poczty e-mail:

sp_processmail - wykorzystując procedury rozszerzone umożliwia odebranie wiadomości e-mail z treścią będącą zapytaniem SQL i odesłanie nadawcy odpowiedzi zawierającej zestaw wyników zapytania

Procedury wykorzystujące usługę SQL Server Agent:

sp_add_alert - dodaje alert (wysyłanie informacji do operatora),
sp_add_category - dodaje kategorię zadań, operatorów lub alertów,
sp_add_job - dodaje nowe zadanie wykonywane przez usługę SQLServerAgent,
sp_add_jobschedule - pozwala ustawić automatyczne wykonanie zadania w określonym czasie,
sp_add_jobstep - dodaje krok (operację) do zadania,
sp_add_operator - dodaje nowego operatora,
sp_delete_alert - usuwa alert,
sp_delete_category - usuwa kategorię,
sp_delete_job - usuwa zadanie,
sp_delete_jobschedule - usuwa zaplanowane automatyczne wykonanie zadania,
sp_delete_jobstep - usuwa krok zadania,
sp_delete_operator - usuwa operatora,
sp_help_alert - wyświetla informacje o alercie,
sp_help_category - wyświetla informacje o kategorii,
sp_help_job - wyświetla listę zadań ustawionych w serwerze,
sp_help_jobschedule - wyświetla listę zaplanowanych automatycznych wykonań zadań w serwerze,
sp_help_jobstep - wyświetla listę kroków w zadaniu,
sp_help_operator - wyświetla informacje o operatorze,
sp_start_job - powoduje natychmiastowe rozpoczęcie wykonywania zadania,
sp_stop_job - powoduje natychmiastowe wstrzymanie wykonywania zadania,
sp_update_alert - pozwala na zmianę ustawień alertu,
sp_update_category - zmienia nazwę kategorii,
sp_update_job - zmienia atrybuty zadania,
sp_update_jobschedule - zmienia ustawienia automatycznego wykonywania zadania,
sp_update_jobstep - zmienia ustawienia kroków zadania,
sp_update_operator - pozwala zmienić dane operatora

Procedury innego przeznaczenia (czasem nazywane po prostu procedurami systemowymi - ale my zarezerwowaliśmy ten termin dla bardziej ogólnej grupy procedur):

sp_addtype - pozwala dodać typ danych użytkownika,
sp_attach_db - podłącza bazę danych do serwera,
sp_configure - pozwala zobaczyć i zmienić globalne ustawienia serwera,
sp_dboption - pozwala zobaczyć i zmienić opcje bazy danych,
sp_detach_db - odłącza bazę danych od serwera,
sp_droptype - usuwa typ danych użytkownika,
sp_help - wyświetla informacje o obiektach bazy danych,
sp_helpconstraint - wyświetla iformacje o ograniczeniach,
sp_helpdb - wyświetla informacje o bazach danych,
sp_helpindex - wyświetla informacje o indeksach w tabeli,
sp_helptrigger - wyświetla informacje o wyzwalaczach w tabeli,
sp_lock - wyświetla informacje o blokadach zasobów,
sp_recompile - rekompiluje procedurę składowaną,
sp_rename - zmienia nazwę obiektu utworzonego przez użytkownika,
sp_renamedb - zmienia nazwę bazy danych,
sp_serveroption - pozwala ustawiać opcje serwerów zdalnych i dołączonych,
sp_settriggerorder - pozwala ustalić kolejność uruchamiania wyzwalaczy w tabeli,
sp_spaceused - pozwala zobaczyć, ile danych znajduje się w tabeli i ile te dane zajmują przestrzeni dyskowej,
sp_who - wyświetla informacje o użytkownikach i procesach w serwerze

Pamiętaj, by przed użyciem procedury koniecznie zapoznać się z jej działaniem - opisu szukaj oczywiście w Books Online. Ponadto weź pod uwagę, że nie wymieniliśmy wszystkich procedur systemowych. Resztę możesz poznać samemu, jeśli zainteresujesz się bardziej środowiskiem MS SQL Server.


Koniecznie przejrzyj listę procedur systemowych. Poczytaj opisy wymienionych przez nas procedur (nie zawadzi przeczytać wszystkich opisów). Wypróbuj te procedury, które wydadzą Ci się przydatne. Nie musisz dołączać skryptu z Twoimi testami procedur systemowych do projektu (w niektórych rozdziałach z pewnością i tak ich użyjesz).


Przejdź dalej



Ćwiczenia > Procedury składowane w MS SQL Server 2000 > Procedury systemowe