Explore a função DATABASEPROPERTYEX () para bancos de dados SQL Server

Explore a função DATABASEPROPERTYEX () para bancos de dados SQL Server


Neste artigo, exploramos uma função SQL para extrair metadados do banco de dados SQL usando a função DATABASEPROPERTYEX.

Introdução

Podemos querer recuperar o valor da propriedade do banco de dados. Você pode se conectar à Instância SQL usando SSMS e obter essas configurações na janela de propriedades. Talvez você não queira usar a GUI, caso precise verificar a propriedade para vários bancos de dados. É possível, mas demorado. Você também não pode exportar os dados em um formato Excel, e novamente isso causa problemas para anotar os valores manualmente:

Propriedades do banco de dados SQL

SQL Server fornece função interna DATABASEPROPERTY e DATABASEPROPERTYEX () para recuperar as informações. É essencial conhecer a diferença nessas funções antes de prosseguir.

O SQL Server 2012 introduziu uma nova função de metadados DATABASEPROPERTYEX. Uma versão anterior, DATABASEPROPERTY, ainda está disponível, mas principalmente para compatibilidade com versões anteriores. O principal problema do DATABASEPROPERTY é que ele retorna apenas o tipo de dados INT na saída. Obtemos a saída do tipo de dados sql_variant na nova função DATABASEPROPERTYEX.

Depois que escrevemos essa função no SSMS, você obtém informações sobre seus argumentos e valores de retorno.

sintaxe da função

Requer dois argumentos:

  • Nome do banco de dados: Especifique o nome do banco de dados para o qual queremos recuperar as informações
  • Nome da propriedade: Especifique o nome da propriedade para obter seu valor configurado no banco de dados especificado no primeiro argumento

Vamos explorar essa função interna usando exemplos.

Exemplo 1: Obter agrupamento de banco de dados

O agrupamento do SQL Server é um conjunto de caracteres e regras de codificação. Você pode ter um agrupamento específico para um banco de dados, dependendo do requisito.

Leia Também  Variáveis ​​de tabela com otimização de memória no SQL Server

Argumento: Agrupamento

Obter agrupamento de banco de dados

Nesse caso, o [AdventureWorks] banco de dados tem um agrupamento SQL_Latin1_General_CP1_CI_AS.

Se não especificarmos um nome de banco de dados no primeiro argumento, ele não fornecerá nenhuma mensagem de erro. Você recebe NULL na saída.

Obter agrupamento de banco de dados para banco de dados em branco

Exemplo 2: verificar o status do banco de dados usando a função DATABASEPROPERTYEX ()

Podemos ter diferentes status de um banco de dados SQL, conforme especificado abaixo:

  • Conectados: Mostra que o banco de dados está disponível para consultas
  • desligada: Às vezes, não queremos um banco de dados disponível para os usuários, mas ele deve existir na instância do SQL. Nesse caso, podemos definir o banco de dados como status offline
  • Restaurando: Podemos ver o status da restauração se um banco de dados estiver sendo restaurado a partir do backup ou se não executamos a recuperação em um banco de dados
  • Suspeito: Em caso de problemas com o banco de dados, como corrupção de arquivo de dados primário ou secundário, é possível ver o banco de dados no modo suspeito
  • Recuperando: Depois que o banco de dados estiver executando um processo de recuperação, você poderá vê-lo no status de recuperação. Você poderá ver esta mensagem caso reinicie o SQL Server enquanto alguma transação ativa estiver em execução

Você pode ler um artigo Noções básicas sobre diferentes estados do banco de dados do SQL Server para obter mais detalhes.

Argumento: Status

Como mostrado aqui, o banco de dados [AdventureWorks] banco de dados está em CONECTADOS status.

Verifique o status do banco de dados usando a função DATABASEPROPERTYEX ()

Exemplo 3: Verifique o acesso do usuário do banco de dados usando a função DATABASEPROPERTYEX ()

Normalmente, um banco de dados permanece no modo multiusuário. Nesse modo, muitos usuários podem se conectar ao banco de dados e executar consultas.

Também podemos definir um banco de dados em um único modo de usuário. Nesse modo, o único usuário com db_owner, dbcreator ou sysadmin pode se conectar a um banco de dados, mas apenas uma única conexão com o banco de dados é permitida.

Além desses modos, podemos definir um banco de dados no RESTRICTED_USER modo. Nesse modo, apenas usuários com db_owner, dbcreator, e sysadmin funções podem se conectar ao banco de dados.

Argumento: UserAccess

Nós temos um banco de dados [AdventureWorks] no modo multiusuário.

Verifique o acesso do usuário do banco de dados usando a função DATABASEPROPERTYEX ()

Exemplo 4: Verifique o status da propriedade Fechamento automático de um banco de dados SQL usando a função DATABASEPROPERTYEX ()

Podemos definir um banco de dados para fechar automaticamente usando o AUTO_CLOSE propriedade definida como true. Nesse caso, o banco de dados é encerrado e libera os recursos do sistema assim que o último usuário sai.

Neste exemplo, vamos ativar AUTO_CLOSE propriedade no meu banco de dados de teste[[Showroom]usando o script abaixo.

Leia Também  Quadros enormes no switch Cisco Nexus 3064PQ |

Agora, verificamos o valor para AUTO_CLOSE usando o argumento IsAutoClose no[[Showroom]e[[AdventureWorks]bancos de dados.

Na captura de tela abaixo, verificamos que o[[Showroom]banco de dados está definido para fechar automaticamente, enquanto[[AdventureWorks]O banco de dados não possui esta propriedade ativada:

Verifique o status da propriedade Fechamento automático de um banco de dados usando a função DATABASEPROPERTYEX ()

Exemplo 5: Verificar o status da propriedade Criar e atualizar automaticamente estatísticas de um banco de dados SQL usando a função DATABASEPROPERTYEX ()

O otimizador de consultas do SQL Server cria as estatísticas automaticamente com base nos predicados da consulta. Podemos ver estatísticas a partir de _WA_sys nome.

Argumento: IsAutoCreateStatistics

Criar estatísticas automaticamente

Argumento: IsAutoUpdateStatistics

Podemos controlar o comportamento do otimizador de consulta para atualizar automaticamente as estatísticas do SQL Server com base em um limite predefinido. Você sempre deve definir esta propriedade ativada, a menos que tenha um motivo específico para desativar as estatísticas de atualização automática.

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br

Na saída, o valor 1 mostra que a estatística de atualização automática está ativada para[[Showroom]base de dados:

Verifique o status da propriedade Criar automaticamente e Atualizar automaticamente estatísticas de um banco de dados usando a função DATABASEPROPERTYEX ()

Exemplo 6: Verifique se o banco de dados é uma cópia apenas de esquema e estatística de um banco de dados SQL

SQL Server 2014 SP2 em diante, podemos criar um banco de dados em branco com a cópia somente de esquema e estatística de um banco de dados do usuário usando DBCC CLONEDATABASE. Podemos usar DATABASEPROPERTYEX para verificar se é um banco de dados clone ou não.

Verifique se o banco de dados é um esquema e se as estatísticas apenas copiam

Em um exemplo anterior, definimos[[Showroom]banco de dados para fechar automaticamente assim que o último usuário sair. Se verificarmos a propriedade clone desse banco de dados, ele retornará NULO saída porque Ele não pode acessar o banco de dados para verificar sua propriedade.

Argumento: IsClone

Argumento IsClone

Exemplo 7: verifique a última data e hora da verificação de consistência para um banco de dados SQL

Devemos executar verificações regulares de consistência de todos os bancos de dados na instância SQL usando DBCC CHECKDB. Ele garante que o banco de dados seja consistente e não tenha corrupção. Podemos verificar o carimbo de data e hora da última verificação bem-sucedida da consistência do banco de dados.

Argumento: LastGoodCheckDbTime

Verifique a última data e hora da verificação de consistência de um banco de dados

No meu ambiente de demonstração, não realizei DBCC CHECKDB no mestre base de dados. Se não realizamos uma DBCC CHECKDB em um banco de dados, ele retorna o padrão saída 1900-01-01 00: 00: 00.000

Saída padrão

Exemplo 8: Verifique o modelo de recuperação de banco de dados para um banco de dados SQL

O SQL Server fornece Completo, com registro em massa, e Simples modelo de recuperação. Ele determina o tipo de backups do banco de dados e a capacidade de recuperar dados em caso de problemas.

Argumento: Recuperação

Na consulta abaixo, verificamos o modelo de recuperação para três bancos de dados – Master, SQLShack e Javatest. Vemos diferentes modelos de recuperação definidos para todos os bancos de dados em uma instância SQL.

Você pode consultar Noções básicas sobre modelos de recuperação de banco de dados do SQL Server para explorar modelos de recuperação.

Verifique o modelo de recuperação de banco de dados para um banco de dados

Exemplo 9: Obter valores para diferentes propriedades de todos os bancos de dados SQL usando a função DATABASEPROPERTYEX

Podemos consultar os documentos da Microsoft para todos os argumentos suportados pelo DATABASEPROPERTYEX função. Neste exemplo, queremos recuperar as configurações para todos os bancos de dados SQL disponíveis na instância SQL. Encontrei um script útil no Microsoft TechNet para ele. Modifiquei este script para incluir também a versão do SQL Server 2019 e anexado a este artigo.

Este script usa um Caso instrução no SQL Server. Você pode consultar a instrução CASE do artigo no SQL para aprender. Ele também obtém o nome do banco de dados para o primeiro argumento usando o parâmetro sys.databases visualização do sistema:

Faça o download do arquivo DATABASEPROPERTYEX.sql aqui

Na saída, podemos ver diferentes propriedades retornadas para todos os bancos de dados SQL usando o DATABASEPROPERTYEX função:

Saída de script

Conclusão

Neste artigo, exploramos informações úteis DATABASEPROPERTYEX função para verificar valores de configuração para bancos de dados SQL. Ele é um script útil e útil, no qual você precisa verificar valores para todos os bancos de dados SQL disponíveis na instância. Não podemos usar o método GUI nesse caso, pois levaria mais tempo. Você pode usar o DATABASEPROPERTYEX para verificar os valores, exportá-lo para CSV, formato Excel e armazená-lo para seus registros.

Rajendra Gupta
Últimas mensagens de Rajendra Gupta (ver todos)

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br