Everyone is DBA (II) SQL Server Metadata

A set of tables is maintained in SQL Server to store information about all objects, data types, constraints, configuration options, available resources, and so on in SQL Server, known as metadata information (Metadata), which are known as system base tables (systems base Tables). In these underlying tables, a subset of the underlying tables that exist in the master database contain system-wide information. The underlying table that exists in a specific database (also containing master db) contains object and resource information that belongs to that particular database.

You can query all system base tables from sys.objects by logging on as a system administrator.

 Use Master; SELECT [name, Type_desc from sys.objectsWHERE= ' system_table ' ORDER  by [name];

System base tables are used only within the database engine and are not intended to provide common functionality, so compatibility may not be guaranteed when changes occur. These table objects prefixed with "SYS", such as sysobjects, sysindexes, sysusers, sysdatabases, etc., are actually compatible views (compatibility View). SQL Server maintains backward compatibility (backward compatibility) by providing a collection of compatible views so that applications built based on these compatible views are not compromised. New features, such as table partitioning, resource control, and so on, will not be added to these views.

Currently, the general interface for SQL Server's recommended access metadata information is the catalog view. All catalog views, including the dynamic management view DMV (Management views) and the compatible view (compatibility), are present in the "SYS" Schema.

SELECTSchema_name (schema_id) asschema_name,[name]  as object_name, Type_desc fromsys.all_objectsWHERESchema_name (schema_id)= 'SYS'     andType_desc= 'VIEW'ORDER  by object_name;

The object that begins with the name "Sys.dm_" is the dynamic management view DMV (Management views). These dynamic management objects have both views and functions, but most of them are views, so they are often referred to as dynamic management view DMV (Management views). The DMV is not a real table based on the database file, but is based on the presentation of the internal database structure. Depending on the scope of responsibility for the information displayed by the DMV object, they are divided into several directories.

• The dm_exec_* contains information related to user code execution and Session.
• dm_os_* contains low-level system information such as Memory, Locking, Scheduler, etc.
• The dm_tran_* contains details about the transaction.
• The dm_io_* contains information about network and disk I/O activity.
• dm_db_* contains details about the database and database objects, such as Index.

Catalog views are built on the inheritance model (inheritance models) so that the common properties of an object do not have to be defined internally. For example, sys.objects contains common properties for various object types, and Sys.tables inherits from Sys.objects, so it first contains columns that are exactly the same as sys.objects, and then adds additional columns for specific object types.

You can download a map diagram of the catalog view at the SQL Server R2 System views Map location.

If you want to query the definition of a catalog view, you can use the Object_definition function or sp_helptext to query.

SELECT object_definition (object_id('sys.tables'));

EXEC ' sys.tables ' ; EXEC ' sys.tables ';

There are some other ways to access SQL Server metadata information:

• Information Architecture view (information schema views)
• Systems functions (System Functions)
• system stored procedures (systems Stored procedures)

For example, query the recovery model for a specified database through a system function.

SELECT Serverproperty ('Edition'  as Edition    , serverproperty (' engineedition '  as engineedition    , Databasepropertyex ('msdb ' Recovery' as Recoverymodel;

Catalog views are the preferred interface for accessing SQL Server metadata information, followed by system functions (systems Functions).

This series of articles "Everyone is a DBA" by Dennis Gao published from the blog Park Personal technology blog, without the author's consent to prohibit any form of reproduction, any automatic or artificial reptile reproduction or plagiarism behavior are bullying.

Everyone is DBA (II) SQL Server Metadata