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.

For example:

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.

For example:

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.

Function Name	description	Example
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

Naming Parttern	description
db	Database related (Relational Database)
io	I/O statistics
Os	SQL Server Operation System Information
‘Tran’	Transaction-related
‘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.

For example:

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.