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