SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

Funkcje, procedury i zmienne systemowe

Oprócz widoków systemowych, za pośrednictwem których, możesz dotrzeć do informacji (metadanych) o środowisku, mamy do dyspozycji szereg innych przydatnych obiektów.

W artykule tym, prezentuję kilka z nich. Podobnie jak widoki systemowe, ich różnorodność daleko wykracza poza zakres tego kursu. Warto mieć świadomość ich istnienia – potraktuj ten rozdział jako wstęp do obiektów, które dają wiedzę na temat środowiska.

Obiekty te, zazwyczaj wykorzystywane są przez administratorów baz danych (DBA DataBase Administrator), ale dla zwykłych użytkowników bywają również pomocne. Wszystkie informacje do których możemy za ich pośrednictwem dotrzeć, dostępne są również poprzez opisane wcześniej widoki systemowe.


Skalarne funkcje systemowe

Systemowe funkcje wbudowane zwracające metadane, stosujemy zazwyczaj w procedurach utrzymaniowych, np. do identyfikacji obiektów, ich parametrów czy wykonywania na nich działań administracyjnych.

Funkcje OBJECT_ID() oraz OBJECT_NAME() pozwalają pójść na skróty w odpytywaniu widoków systemowych.
Zwracają identyfikator obiektu lub jego nazwę. Bywają przydatne, szczególnie jeśli na szybko, chcemy wyciągnąć jakieś dane, a nie znamy identyfikatora danego obiektu i nie chce nam się łączyć dodatkowych widoków.

Dla przykładu – chcemy na szybko wyświetlić informacje o wszystkich indeksach w tabeli dbo.Orders. W skrótowej wersji, moglibyśmy zapisać zapytanie w taki sposób :

USE Northwind
GO
 
SELECT OBJECT_NAME(object_ID) AS TableName, * 
FROM sys.indexes
WHERE OBJECT_NAME(object_ID) = 'Orders'

Metadane_01

Co prawda od razu muszę tutaj przestrzec, że nie do końca jest to sposób idealny. Jak to bywa ze skrótami, czasem mogą nas wyprowadzić w pole.

Zauważ, że wyszukałem wszystkie indeksy dla tabeli Orders, bez podania nazwy schematu. W sytuacji, w której mielibyśmy dwie tabele o tych samych nazwach (tyle że w różnych schematach) wynik byłby mylący, bo zwrócone rekordy dotyczyły by wszystkich indeksów w obu tabelach. Warto o tym pamiętać.

Funkcje DB_ID() oraz DB_NAME(), analogicznie jak w przypadku poprzednio prezentowanych funkcji, zwracają translacje nazwy na identyfikator lub odwrotnie. Samo wywołanie, bez podania parametru, zwróci aktualną nazwę / identyfikator bazy danych do której jesteś podłączony.

SELECT		DB_ID() as ID_bazy, 
		DB_NAME() as Nazwa_bazy, 
		DB_NAME(1) as NazwaBazyId1

Metadane_02

Funkcje sprawdzające, aktualne parametry bazy danych DATABASEPROPERTEX() oraz serwera SERVERPROPERTY(). Wywołując je z odpowiednimi argumentami, możemy otrzymać informacje, np. na temat sposobu porównywania wartości tekstowych (COLLATION).

SELECT DATABASEPROPERTYEX ( 'Northwind', 'Collation' ) as DBCollation,
	   SERVERPROPERTY ('Collation' ) as SrvCollartion

Metadane_03


Zmienne @@GLOBALNE

Zmienne globalne to bardzo podobne do funkcji skalarnych obiekty. Ich nazwa zaczyna się od @@ i jest ich raptem kilkadziesiąt. Poniżej przykład informacji o środowisku zwracanych przez tego typu obiekty :

SELECT	@@SERVICENAME as InstanceName, 
	@@VERSION as SQLServerVersion

Oprócz zmiennych typowo informacyjnych o środowisku, mamy kilka wykorzystywanych w logice programistycznej. Poniżej przykład wykorzystania @@TRANCOUNT, aby określić liczbę otwartych transakcji bieżącego połączenia.

SELECT @@TRANCOUNT as LiczbaOtwartychTransakcji
 
BEGIN TRANSACTION  --nowa transakcja
 
SELECT @@TRANCOUNT as LiczbaOtwartychTransakcji

Metadane_04Metadane_05


Procedury systemowe

Na koniec bardzo użyteczne źródło informacji dla każdego DBA dotyczące różnych aspektów baz, serwera czy aktywności użytkowników. Procedury systemowe to nic innego jak kompilacja widoków, mająca na celu w zwięzły i przystępny sposób, prezentować najważniejsze informacje.

Zamiast pisać kwerendy łączące wiele widoków systemowych, możemy skorzystać z istniejących procedur, wywołując je po prostu po nazwie.

Poniżej kilka dwa przykłady wyświetlania informacje o bazie Northwind :

exec sp_helpdb 'Northwind';
 
exec sp_spaceused;

Metadane_06
Jak widać forma – typowo dla administratorów. Każda z powyższych procedur zwraca osobne rekordsety. Procedura sp_spaceused, może być wywołana również dla konkretnego obiektu (tabeli) w bazie danych. Zwróci nam informacje o liczbie rekordów i miejscu alokowanym przez dany obiekt.

exec sp_spaceused 'dbo.Orders'

Metadane_07

Jedną z fajniejszych rzeczy, jest możliwość modyfikacji lub podglądu zawartości istniejących, wbudowanych procedur systemowych. W ten sposób możesz odkryć strukturę widoków systemowych. Definicje procedur, funkcji systemowych znajdziesz w bazie master.

Szczegółowe omówienie tych struktur, prezentuję na kursach z programowania i administracji SQL Server na które zapraszam.

Zdobywanie informacji o środowisku

Metadane to dane – o danych, służące do opisania składowanych w bazach informacji. Przyznam, że definicja ta (powielana w wielu publikacjach), gdy odkrywałem temat baz danych, była dla mnie masłem maślanym w czystej postaci.

W rozdziale tym, postaram się w bardziej przystępny sposób wyjaśnić sens i cel istnienia metadanych. Zaprezentuję także kilka praktycznych przykładów korzystania ze struktur, pozwalających poznać środowisko bazodanowe.


Dane o danych

Na początek, zastanówmy się czym właściwie są dane składowane w naszych bazach. Patrząc na nie w kompletnym oderwaniu od kontekstu, dane to zbiór cyfr (np. typu całkowitego), znaków (ciągi tekstowe), bitów i bajtów. Jeśli w ten sposób na nie spojrzymy – staną się chaotyczne i kompletnie bezużyteczne.

Wartość tak postrzeganych informacji będzie analogiczna, jak atomów z których składa się człowiek. W końcu to też parę kilo węgla, wodoru, tlenu i innych pierwiastków. Wartość tych atomów w kontekście otaczającego nas świata – zerowa.

Prawdziwy sens naszym danym, nadają właśnie metadane. Dzięki nim, te pojedyncze atomy, łączą się w informacje, a z nich możemy czerpać wiedzę. Do odkrycia tych powiązań, poprawnej interpretacji, potrzebujemy opisu = danych o danych.

Dzięki metadanym, wiemy jak interpretować wartość liczbową 1980 w tabeli Pracownicy w określonej kolumnie. Może to być rok urodzin, śmierci czy zatrudnienia albo wysokość podstawowego wynagrodzenia. Bez metadanych nie ma użytecznych informacji.

Czasem ta granica czym są metadane a dane właściwe jest dość płynna. Weźmy inną analogię – wszystkie „dodane” informacje, opisujące jakaś książkę. Metadanymi z pewnością może być jej tytuł, autor, wydawnictwo, rok i nakład. Z drugiej zaś strony, w bibliotece, te informacje mogą być danymi właściwymi… Podobnie jak w systemie zarządzającym setkami czy tysiącami baz danych, metadane z punktu widzenia pojedynczego obiektu, mogą stać się głównymi informacjami.


Metadane w SQL Server

Podobnie jak w innych serwerach bazodanowych, metadane dotyczą wszelkich struktur spotykanych w tych systemach. Idąc od najniższego poziomu – będą to nazwy i typy kolumn, tabel czy samych baz danych. To także informacje o środowisku serwera, wersji, użytkownikach czy loginach.
W SQL Server dostępnych mamy szereg różnych obiektów, za pomocą których możemy dotrzeć do informacji nadających sens składowanym danym. Opisuje je w osobnych artykułach, przedstawiając najczęściej wykorzystywane w praktyce

  • WIDOKI SYSTEMOWE są podstawowym i najważniejszym źródłem informacji. W wersji SQL Server 2012 jest ich kilkaset ! Dotyczą one wszelkich obiektów tworzonych w ramach serwera, ale także procesów czy statystyk.
  • FUNKCJE, PROCEDURY I ZMIENNE SYSTEMOWE – to obiekty, które stosujemy głównie w skryptach czy np. procedurach utrzymaniowych. Umożliwiają pójście na skróty w stosunku do widoków.

Widoki systemowe w SQL Server

Jednym z najbardziej użytecznych źródeł informacji dotyczących struktur i właściwości obiektów (metadane) stworzonych w bazach SQL Server są widoki systemowe. Przeciętni użytkownicy rzadko kiedy korzystają z ich możliwości. Być może jest to związane z liczbą widoków, różnymi typami i koniecznością poznania ich struktur. A może po prostu nie mają świadomości ich istnienia lub nie są im do niczego potrzebne?

Ilość widoków, z których możemy czerpać wiedzę jest z każdą wersją SQL Server większa. W końcu przybywa samych funkcjonalności a więc i wiedzy na temat potencjalnych obiektów i struktur.

Pełną mapę dostępnych widoków systemowych, relacji między nimi dla SQL Server 2008 R2 możesz znaleźć tutaj.

W artykule tym, znajdziesz ogólne informacje na temat typów widoków systemowych. Zaprezentuje, też kilka przykładów, które mogą okazać się pomocne w oswojeniu tego niezmiernie wartościowego źródła wiedzy, nie tylko dla programistów czy administratorów.


Rodzaje widoków systemowych w SQL Server

W środowisku SQL Server, możemy korzystać z kilku typów widoków systemowych. Jak to zazwyczaj bywa, jest wiele dróg do osiągnięcia tego samego celu.

Ogólne widoki systemowe

To podstawowe struktury, dzięki którym mamy bezpośredni dostęp do wiedzy na temat wszystkich obiektów w bazach danych / serwerze (metadanych). Zawierają się w schemacie sys. Co ważne, są one bardzo dobrze udokumentowane w BOL / MSDN. Ich liczba jest dość duża, dostęp do nich znajdziesz w każdej bazie, nawigująć np w Management Studio do Views > System Views. Fragment (akurat widoków ze INFORMATION_SCHEMA) poniżej :
system_views_00
W zależności od poziomu uprawnień, możesz mieć dostęp do cześć z nich lub nawet tylko do pewnego zakresu danych w ich ramach.

Jednym z przykładowych zastosowań jest poznawanie struktury bazy danych. Ten proces, może być dość uciążliwy, szczególnie wtedy, gdy nie mamy dostępu do dokumentacji czy diagramów relacji.

Zdarzają się sytuacje, w których potencjalnie jesteśmy w stanie odgadnąć lub wręcz znamy nazwę jakiejś kolumny, ale nie wiemy w jakiej tabeli może być ona przechowywana. Tego typu przypadków jest wiele (poznawanie relacji między tabelami bez kluczy / z kluczami etc).

Wyobraźmy sobie, bazę zawierającą setki tabel. Chcemy znaleźć tabele w których przechowywane są informacje np. o cenach. Informacje o strukturze każdego obiektu typu tabela, możemy wyciągnąć z dwóch widoków systemowych – sys.columns oraz sys.tables.

USE NorthWind
Go
-- jak znaleźć nazwę tabeli, zawierającą określoną nazwę kolumny
select t.name as TabName, c.name as ColName
from sys.columns c 
	inner join sys.tables t on c.object_id=t.object_id
where c.name like '%price%'

System_Views_01
Sposób ten można jeszcze uprościć, uzyskując identyczny efekt z wykorzystaniem funkcji systemowej OBJECT_NAME.

select  OBJECT_NAME(c.object_id) as TabName,c.name as ColName 
from sys.columns c 
where c.name like '%price%'

Widoki schematu informacyjnego (INFORMATION_SCHEMA)

Jest to ustandaryzowany (ISO) podzbiór bardzo ogólnych widoków systemowych, znajdujących się w schemacie INFORMATION_SCHEMA. Za ich pośrednictwem mamy dostęp do wycinka danych, do których moglibyśmy dotrzeć przez ogólne widoki systemowe. Ilość i zakres danych do których możemy dotrzeć za ich pomocą, jest jednak znacznie uboższa.

Głównym powodem ich istnienia jest próba ułatwienia pracy administratorów baz danych i aplikacji korzystających z różnych systemów. W zamyśle mają ułatwiać odnalezienie się w nieznanym środowisku. Jednak nie jest to sugerowany sposób korzystania z metadanych. Lepiej użyć standardowych widoków systemowych . Są one znacznie bogatsze jeśli chodzi o ilość informacji na temat struktur. Ponadto zapewniają bezpośredni dostęp do metadanych (najbardziej efektywny).

Mały przykład – bardzo ogólne informacje na temat wybranych tabel w bazie Northwind

select * from INFORMATION_SCHEMA.TABLES
where TABLE_NAME like 'C%'

INFORMATION_SCHEMA
Zauważ, że ten widok, to tak naprawdę pochodna, podzbiór danych z podstawowych widoków systemowych : sys.tables oraz sys.views. Praktycznie ten sam rezultat możesz otrzymać w ten sposób :

select DB_NAME() as TABLE_CATALOG,
	SCHEMA_NAME() as TABLE_SCHEMA,
	NAME as TABLE_NAME, 
	type_desc as TABLE_TYPE
from sys.tables
where NAME like 'C%'
UNION
select DB_NAME() as TABLE_CATALOG,
	SCHEMA_NAME() as TABLE_SCHEMA,
	NAME as TABLE_NAME, 
	type_desc as TABLE_TYPE 
from sys.views
where NAME like 'C%'

System_Views_02
Oczywiście to tylko przykład, mający na celu demonstrację, że do tego samego rozwiązania można dotrzeć na różne sposoby.

Widoki zgodności (Compatibility Views)

Są to widoki znane z wersji SQL Server 2000. Dostępne w kolejnych wersjach SQL Server, głównie aby ułatwić migracje / kompatybilność wstecz. Ich stosowanie w nowych, komercyjnych systemach nie jest zalecane. Nie zmienia to faktu, że dla wielu są wciąż cenne i z niektórymi (również mi) trudno jest się rozstać (np. sys.sysprocesses). Łatwo je rozpoznać po nazwie. Są umieszczone w schemacie sys, a ich nazwa również zaczyna się od sys. Kilka przykładów, pokrewnych widoków „prawie” (robi różnicę) analogicznych. Fragmenty wyników zapytań z widoków :

--old one, SQL Server 2000 - compatibility view
select * from sys.sysdatabases

System_Views_03

select * from sys.databases

System_Views_04
Inne przykładowe, analogiczne widoki systemowe vs widoki zgodności :

select * from sys.sysobjects --old
select * from sys.objects
 
select * from sys.syscomments --old
select * from sys.sql_modules
select * from sys.check_constraints

Korzystanie z nich inaczej niż tylko jako źródło wiedzy AdHoc (pojedyncze zapytania) mija się z celem bo mają być one w przyszłości usunięte.
Pełną informację o relacjach pomiędzy widokami zgodności a obecnymi widokami systemowymi znajdziesz tutaj.

Dynamiczne widoki DMV (Dynamic Management Views)

Wprowadzone w SQL Server 2005, są nieocenionym źródłem informacji o procesach, licznikach, wiedzy związanej z wydajnością, stanem bazy, obiektów i samej instancji SQL Servera. Dzięki nim możemy znaleźć wąskie gardła systemu, wykryć nieprawidłowości procesów, statystyki i generalnie dowiedzieć się wszystkiego o stanie SQL Server.

Pogrupowane są ze względu na obiekty i zastosowania którego dotyczą.
Znajdziesz na przykład szereg widoków dynamicznych związanych z operacjami IO (grupa sys.dm_io), systemie operacyjnym SQLOS (grupa sys.dm_os) czy z wykonywaniem zapytań \ zadań (sys.dm_exec). Wiele z nich występuje w postaci funkcji tabelarycznych.

Widoki dynamiczne to także świetne źródło wiedzy na temat statystyk, czy stanu obiektów w bazie np. fragmentacji indeksów.

USE Northwind
GO
 
-- Informacje o stopniu fragmentacji indeksu w tabeli dbo.Orders
 
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'dbo.Orders'),
     NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO

DMV_fragmentation