Query Metadata and Dynamyic Management Object in SQL Server
Properties of an object such as a table or view are stored in a special system table. These attributes are called metadata. All SQL objects have metadata. These metadata are viewable by system views, which are predefined views of SQL Server
There are more than 230 different system views and they are automatically added to the database created by the user. These views are grouped into several different schemas as follows.
System Catalog Views
These views contain information about the table of contents within the SQL Server system. Catalogs are similar to inventory or objects. These views contain a large amount of metadata. In previous versions of SQL Server, to get this data users needed to query large amounts of data in system tables, system views, system functions. From version 2021 onwards, all users can easily query the catalog metadata to search.
SELECT name,object_id,type,type_desc FROM sys.tables;
Information Schema Views
Users can query schemaview information and return system metadata. These views are useful when communicating with third parties. The schema views information allows applications to function correctly despite significant changes to the underlying system tables.
The table below will help us decide whether to query SQL Server-specific system views or information schema views:
|Information Schema Views||SQL Server System Views|
|They are stored in their own Schema, INFORMATION_SCHEMA||They appear in the sys schema.|
|It uses standard terminology instead of SQL Server terminology. For example, use a catalog instead of a database and a domain instead of a user-defined data type.||They follow SQL Server terminology.|
|They may not show all the metadata available to SQL Server’s own catalog views. For example, the sys column includes attributes for the identity and computed column properties, while the INFORMATION_SCHEMA columns do not.||They can display all the metadata available to SQL Server catalog views.|
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES;
System Metadata functions
In addition to views, SQL Server provides several built-in functions that return metadata for a query. These include scalar and table-valued functions, which can return information about system settings, session preferences, and a variety of other objects.
SQL Server metadata functions come in a variety of formats, Some seem similar to standard scalar functions, such as ERROR_NUMBER(). Other functions use special prefixes, such as @@VERSION or $PARTITION.
|OBJECT_ID(<object_name>)||Returns the ID of the database object||OBJECT_ID(‘Sales.Customer’)|
|OBJECT_NAME(<object_id>)||Returns the corresponding name of an object ID||OBJECT_NAME(197575742)|
|@@ERROR||Returns 0 if the last statement was successful, otherwise returns an error code||@@ERROR|
|SERVERPROPERTY(<property>)||Returns the specified value of the server . attribute||SERVERPROPERTY(‘Collation’)|
Example using SELECT statement to query system metadata:
SELECT SERVERPROPERTY('EDITION') AS EditionName;
One of the new features in SQL Server 2019 is memory-optimized tempdb metadata. The SQL Server team enhanced the tempdb code with optimizations so that some metadata that can be a bottleneck on heavy tempdb systems can now rely on memory and is optimized for RAM access.
High volume, large scale environments that use a lot of tempdb often experience this kind of bottleneck, Before this this would require some kind of solution to reduce tempdb usage. However, with this new feature, it is possible to allow metadata to stay in memory and be accessed optimally.
Dynamically managed object query
First introduced by SQL Server 2006, Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs) are dynamic management objects that return server information or database state information. DMVs and DMFs are collectively referred to as dynamic managed objects. They provide insight into software operations and can be used to check the status of SQL Server instances, troubleshoot problems, and tune performance.
Both DMVs and DMFs return tabular data but the difference is that the DMF takes at least one parameter, the DMV takes no parameters. SQL Server 2019 provides nearly 200 dynamic management objects. To query DMVs, VIEW SERVER STATE or VIEW DATABASE STATE permission will be required, based on the scope of the DMV.
Cataloging and querying DMVs
List of conventions to help organize DMVs using function
|db||Database related (Relational Database)|
|Os||SQL Server Operation System Information|
|‘exec’||Query execution-related metadata|
To query a dynamic management object, you use the SELECT statement as you would with any view or multi-valued table object. Example: The code below returns a list of current user connections from sys.dm_ex .
sys.dm_exec_sessions is a server-scoped DMV that displays information about all active user connections and internal actions. This information includes logged in user, current session settings, client version, client program name, client login time, etc. sys.dm_exec_sessions can be used to identify a session specific session and find information about that session.
SELECT session_id, login_time, program name FROM sys.dm_exec_sessions WHERE login_name='sa' and is_user_process = 1;
Here, is_user_process is a column in the view that determines whether the session is a system session or not. A value of 1 indicates that it is not a system session but a user session. The program_name column defines the name of the client program that initiated the session, The login_time column sets the time when the session started.