As propriedades de um objeto, como uma tabela ou visualização, são armazenadas em uma tabela de sistema especial. Esses atributos são chamados de metadados. Todos os objetos SQL têm metadados. Esses metadados podem ser visualizados por exibições do sistema, que são exibições predefinidas do SQL Server

São mais de 230 visualizações de sistema diferentes e elas são adicionadas automaticamente ao banco de dados criado pelo usuário. Essas exibições são agrupadas em vários esquemas diferentes, conforme a seguir.

Visualizações do Catálogo do Sistema

Essas exibições contêm informações sobre o índice no sistema SQL Server. Catálogos são semelhantes a inventários ou objetos. Essas visualizações contêm uma grande quantidade de metadados. Nas versões anteriores do SQL Server, para obter esses dados, os usuários precisavam consultar grandes quantidades de dados em tabelas do sistema, visualizações do sistema, funções do sistema. A partir da versão 2021, todos os usuários podem consultar facilmente os metadados do catálogo para pesquisar.

Por exemplo:

 SELECT name,object_id,type,type_desc FROM sys.tables;

Visualizações do esquema de informações

Os usuários podem consultar informações do schemaview e retornar metadados do sistema. Essas visualizações são úteis ao se comunicar com terceiros. As informações de visualizações de esquema permitem que os aplicativos funcionem corretamente apesar das alterações significativas nas tabelas de sistema subjacentes.

A tabela abaixo nos ajudará a decidir se devemos consultar exibições de sistema específicas do SQL Server ou exibições de esquema de informações:

Por exemplo:

 SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES;

Funções de metadados do sistema

Além das exibições, o SQL Server fornece várias funções internas que retornam metadados para uma consulta. Isso inclui funções escalares e com valor de tabela, que podem retornar informações sobre configurações do sistema, preferências de sessão e uma variedade de outros objetos.

As funções de metadados do SQL Server vêm em vários formatos, algumas parecem semelhantes às funções escalares padrão, como ERROR_NUMBER(). Outras funções usam prefixos especiais, como @@VERSION ou $PARTITION.

Exemplo usando a instrução SELECT para consultar metadados do sistema:

 SELECT SERVERPROPERTY('EDITION') AS EditionName;

Um dos novos recursos do SQL Server 2019 são os metadados tempdb com otimização de memória. A equipe do SQL Server aprimorou o código tempdb com otimizações para que alguns metadados que podem ser um gargalo em sistemas tempdb pesados agora possam depender da memória e sejam otimizados para acesso à RAM.

Ambientes de alto volume e grande escala que usam muito tempdb geralmente experimentam esse tipo de gargalo. Antes disso, isso exigiria algum tipo de solução para reduzir o uso de tempdb. No entanto, com esse novo recurso, é possível permitir que os metadados permaneçam na memória e sejam acessados de forma otimizada.

Consulta de objeto gerenciada dinamicamente

Introduzidos pela primeira vez pelo SQL Server 2006, Dynamic Management Views (DMVs) e Dynamic Management Functions (DMFs) são objetos de gerenciamento dinâmico que retornam informações do servidor ou informações de estado do banco de dados. DMVs e DMFs são chamados coletivamente de objetos gerenciados dinâmicos. Eles fornecem informações sobre as operações do software e podem ser usados para verificar o status das instâncias do SQL Server, solucionar problemas e ajustar o desempenho.

Ambos DMVs e DMFs retornam dados tabulares, mas a diferença é que o DMF leva pelo menos um parâmetro, o DMV não recebe parâmetros. O SQL Server 2019 fornece quase 200 objetos de gerenciamento dinâmico. Para consultar DMVs, será necessária a permissão VIEW SERVER STATE ou VIEW DATABASE STATE, com base no escopo do DMV.

Catalogando e consultando DMVs

Lista de convenções para ajudar a organizar DMVs usando função

Para consultar um objeto de gerenciamento dinâmico, use a instrução SELECT como faria com qualquer exibição ou objeto de tabela com vários valores. Exemplo: O código abaixo retorna uma lista de conexões de usuário atuais de sys.dm_ex .

sys.dm_exec_sessions é uma DMV com escopo de servidor que exibe informações sobre todas as conexões de usuário ativas e ações internas. Essas informações incluem usuário conectado, configurações de sessão atuais, versão do cliente, nome do programa cliente, hora de login do cliente, etc. sys.dm_exec_sessions pode ser usado para identificar uma sessão específica de sessão e encontrar informações sobre essa sessão.

Por exemplo:

 SELECT session_id, login_time, program name FROM sys.dm_exec_sessions WHERE login_name='sa' and is_user_process = 1;

Aqui, is_user_process é uma coluna na exibição que determina se a sessão é uma sessão do sistema ou não. Um valor de 1 indica que não é uma sessão do sistema, mas uma sessão do usuário. A coluna program_name define o nome do programa cliente que iniciou a sessão. A coluna login_time define a hora em que a sessão foi iniciada.