Everyone is DBA (II) SQL Server Metadata

Source: Internet
Author: User
Tags server memory

The original: 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).

Everyone is a DBA the index of the article series:

Serial number



Everyone is a DBA (I) SQL Server Architecture


Everyone is DBA (II) SQL Server Metadata


Everyone is a DBA (III) SQL Server Scheduler


Everyone is a DBA (IV) SQL Server memory Management


Everyone is a DBA (V) SQL Server database files


Everyone is a DBA (VI) SQL Server transaction log


Everyone is DBA (VII) B-Tree and + + Tree


Everyone is a DBA (VIII) SQL Server page storage structure


Everyone is DBA (IX) Server information collection script compilation


Everyone is a DBA (X) Resource Information Collection script compilation


Everyone is DBA (XI) I/O information collection script compilation


Everyone is DBA (XII) Query information collection script compilation


Everyone is a DBA (XIII) index information collection script compilation


Everyone is a DBA (XIV) Stored Procedure information collection script compilation


Everyone is DBA (XV) lock information Collection script compilation

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.