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'

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

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

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

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;

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'

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.

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 :

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%'

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%'

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%'

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

select * from sys.databases

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