SQL Server Data Dictionary

Question

Hello, I come from Oracle culture and want to learn a little SQL Server, I’m having difficulty finding the data dictionary of SQL Server, I’ve searched in other forums and found nothing.

In Oracle we have a data dictionary that shows all options of system views, such as which view lists all tables in the database, which view lists all users, which view lists all tables contraints etc.. and usually when I don’t know the name of the view I do a (select table_name from dictionary where table_name like '%palavra_chave_aqui%') and I end up finding.

What is the equivalent in SQL Server for me to list all system views?

Answer 1

In Oracle we have a data dictionary that shows all the options of system views, (...) What is the equivalent in SQL Server to list all system views?

This form is a particular implementation of Oracle Database.

As stated in the Wikipedia entry, "In relational databases the information scheme (information_schema) is an ANSI standard set of read-only views that provide information about all tables, views, columns and procedures in a database". SQL Server allows metadata to be obtained by this standard.

Each database in SQL Server contains its own information schema, called INFORMATION_SCHEMA. One way to obtain metadata is to

SELECT ...
  from bancodedados.INFORMATION_SCHEMA.objeto
  where ...

The objects available under the ISO standard are detailed in the SQL Server documentation, starting with System Information Schema Views. For example, to list tables in a database there is the object TABLES.

In addition to SQL Server there are other database managers that allow to obtain the metadata by the same method, such as Mysql, Mariadb, Postgresql etc.

There are other ways to get the metadata in SQL Server, including with more information, but as an introduction it seems to me that the use of INFORMATION_SCHEMA will be sufficient. However, if you are interested in deepening the SQL Server metadata, you will find documentation on System Catalog Views.

Articles:

• What is a SQL Server Data Dictionary?
• SQL Server System Views: The Basics