co to jest słownik danych?

w SQL Server słownik danych jest zbiorem tabel bazodanowych służących do przechowywania informacji o definicji bazy danych. Słownik zawiera informacje o obiektach bazy danych, takich jak tabele, indeksy, kolumny, typy danych i widoki.

słownik danych jest używany przez SQL Server do wykonywania zapytań i jest automatycznie aktualizowany za każdym razem, gdy obiekty są dodawane, usuwane lub zmieniane w bazie danych.

wszystkie przykłady tego artykułu są oparte na Microsoft SQL Server Management Studio i Bazie Danych AdventureWorks2012. Możesz zacząć korzystać z tych bezpłatnych narzędzi, korzystając z mojego przewodnika Pierwsze kroki przy użyciu SQL Server.

jak SQL Server używa słownika danych

SQL Server używa słownika bazy danych do weryfikacji instrukcji SQL. Podczas wykonywania instrukcji SQL DBMS (System zarządzania Bazą Danych) przetwarza instrukcję, a następnie określa, czy tabele i pola, do których się odwołujesz, są poprawne. Aby to zrobić szybko odwołuje się do słownika danych.

oprócz testowania poprawności instrukcji, SQL Server używa słownika danych do wspomagania generowania planu zapytań i odwoływania się do informacji definiujących strukturę bazy danych.

słownik danych staje się przewodnikiem, per se, dla SQL Server do korzystania z dostępu do danych. Mówiąc najprościej, bez słownika danych, chociaż SQL Server znałby i rozumiał język SQL, nie wiedziałby o tablicach i kolumnach bazy danych, dlatego nie byłby w stanie ich odpytywać.

inne zastosowania słownika danych

ponieważ słownik danych zawiera definicję bazy danych, jest to naprawdę dobry zasób do wykorzystania w celu uzyskania informacji o bazie danych. Naprawdę fajną rzeczą jest to, że słownik danych składa się z tabel i widoków SQL. Oznacza to, że możesz uzyskać informacje o DB poprzez zapytania!

słowniki danych są używane przez projektantów i programistów do zrozumienia struktury bazy danych. Możesz myśleć o słowniku jako o aktualnym dokumencie referencyjnym.

narzędzia projektowe, takie jak SQL Server Management Studio, wyświetlają informacje o bazach danych za pośrednictwem Eksploratora obiektów przy użyciu słownika danych.

tabele wymienione powyżej nie są magicznie znane, a raczej Eksplorator obiektów wysłał zapytanie do słownika danych, aby pobrać wszystkie tabele użytkowników.

słownik danych jest przechowywany w szeregu tabel systemowych. Chociaż można bezpośrednio odpytywać te tabele, Microsoft zastrzega sobie prawo do modyfikowania tabel systemowych składających się na słownik danych. Z tego powodu zalecają odpytywanie widoków INFORMATION_SCHEMA w przeciwieństwie do bezpośredniego dostępu do tabel.

ponieważ możesz samodzielnie odpytywać słownik danych, możesz odpowiedzieć na kilka pytań, które w przeciwnym razie wymagałyby dużo polowania i dziobania za pomocą Eksploratora obiektów. Na przykład, jak łatwo znaleźć wszystkie tabele i widoki za pomocą kolumny BusinessEntityID? Bez słownika danych będziesz musiał użyć Eksploratora obiektów i otworzyć każdą tabelę oraz przeglądać definicje kolumny. Jednak za pomocą słownika danych można to zrobić za pomocą prostego zapytania.

widoki INFORMATION_SCHEMA

widoki INFORMATION_SCHEMA zawarte w SQL Server są zgodne ze standardem ISO SQL-92. Oznacza to, że inni dostawcy baz danych, którzy są zgodni ze standardem ISO, będą dostarczać ten sam zestaw widoków.

oto niektóre z częściej używanych widoków i ich opisy:

• COLUMNS – zwraca jeden wiersz dla każdej kolumny, do której bieżący użytkownik ma dostęp w bieżącej bazie danych. Przy pomocy tego widoku można określić typ danych oraz tabelę, której kolumna ma być użyta.
• tabele – zwraca jeden wiersz dla każdej tabeli, do której użytkownicy mają dostęp w bieżącej bazie danych. Uwaga, zarówno tabele, jak i widoki są zwracane za pomocą widoku tabele.
• VIEW_TABLE_USAGE – zwraca jeden wiersz dla każdej tabeli, która jest używana w widoku w bieżącej bazie danych.
• widoki – zwraca jeden wiersz dla widoków, do których można uzyskać dostęp przy użyciu uprawnień bieżącego użytkownika z bieżącej bazy danych.

przykłady za pomocą widoki INFORMATION_SCHEMA

Lista wszystkich tabel, które zawierają określoną kolumnę

można to zrobić za pomocą INFORMATION_SCHEMA.Widok kolumn. Na przykład poniższe listy Wszystkie tabele i widoki zawierają kolumnę Businessentity id

SELECT TABLE_NAMEFROM AdventureWorks2012_Data.INFORMATION_SCHEMA.COLUMNSWHERE COLUMN_NAME = 'BusinessEntityID'

jak można sobie wyobrazić słownik danych może się przydać! Czy możesz sobie wyobrazić konieczność ręcznego przeszukiwania każdej definicji, aby znaleźć wszystkie wystąpienia pola w bazie danych?

Lista wszystkich tabel w bazie

użyj INFORMATION_SCHEMA.Widok tabel, aby to zrobić.

SELECT TABLE_NAME, TABLE_TYPEFROM AdventureWorks2012_Data.INFORMATION_SCHEMA.TABLESORDER BY TABLE_NAME

to zapytanie zwraca zarówno podstawowe tabele, jak i widoki.

lista liczba tabel w każdym schemacie

w tym przykładzie wymieniono każdy schemat oraz liczbę tabel i widoków zdefiniowanych w nich:

SELECT TABLE_SCHEMA, Count(TABLE_SCHEMA)FROM AdventureWorks2012_Data.INFORMATION_SCHEMA.TABLESGROUP BY TABLE_SCHEMA

widoki katalogów SQL Server

widoki katalogów są kolejnym sposobem przeglądania słownika danych. Uważam, że widoki katalogów obiektów dostarczają więcej informacji i zwykle używam tych widoków zamiast widoków INFORMATION_SCHEMA.

niektóre z widoków, które uznałem za najbardziej przydatne, to:

• objects – zawierają wiersz dla każdego obiektu, taki jak klucz obcy lub ograniczenie klucza podstawowego zdefiniowane w bazie danych.
• kolumny-zawierają wiersz dla każdej kolumny obiektu, takiego jak widok lub tabele.
• tabele – zwróć wiersz dla każdego obiektu tabeli

przykłady przy użyciu widoków katalogów

poniższe przykłady wykorzystują widoki katalogów Microsoft SQL Server, aby uzyskać te same informacje, które zostały użyte powyżej przy użyciu INFORMATION_SCHEMA.

Lista wszystkich tabel, które zawierają określoną kolumnę

w celu uzyskania nazw tabel przy użyciu określonej kolumny, zarówno sys.tabele i sys.widok kolumn musi być używany razem. Są one łączone poprzez object_id, który jest używany do identyfikacji popularnych obiektów bazy danych, takich jak tabele i widoki.

SELECT t.name, t.type_descFROM AdventureWorks2012_Data.sys.columns AS cINNER JOIN sys.tables AS t ON c.object_id = t.object_idWHERE c.name = 'BusinessEntityID'

Lista wszystkich tabel w bazie danych

sys.przy pomocy widoku tabele można uzyskać nazwy wszystkich tabel zdefiniowanych w bazie danych. Ten widok zwraca zarówno tabele bazowe, jak i widoki.

SELECT name, type_descFROM AdventureWorks2012_Data.sys.tablesORDER BY Name

Lista numerów tabel w każdym schemacie

sys.widok tabel nie zawiera nazwy schematu, więc wbudowana funkcja NAZWA_ schematu jest używana do jej uzyskania.

SELECT SCHEMA_NAME(schema_id), count(name)FROM AdventureWorks2012_Data.sys.tablesGROUP BY SCHEMA_NAME(schema_id)

jak widać na przykładzie, jest to trochę bardziej techniczne niż użycie INFORMATION_SCHEMA. Z doświadczenia jednak mówię, że w widokach katalogów jest znacznie więcej informacji … jeśli masz konkretne pytanie dotyczące struktury bazy danych, to są to tabele do trafienia.