Aprenda SQL: O banco de dados INFORMATION_SCHEMA

Aprenda SQL: O banco de dados INFORMATION_SCHEMA

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


A melhor maneira de explicar o que é o banco de dados INFORMATION_SCHEMA seria: “Esse é o banco de dados sobre bancos de dados. É usado para armazenar detalhes de outros bancos de dados no servidor “. O que isso significa, como podemos usá-lo e o que podemos fazer com esses dados é o tópico do artigo de hoje.

O Modelo

Como sempre, começaremos com o modelo de dados primeiro. Este é o mesmo modelo que usamos até agora nesta série, então eu
não vou descrevê-lo novamente.

o banco de dados INFORMATION_SCHEMA - o modelo de dados que usaremos

Ainda assim, uma coisa é interessante. Hoje não estaremos interessados ​​nos dados armazenados nas tabelas, mas em como isso
modelo é descrito no banco de dados INFORMATION_SCHEMA.

O que é o banco de dados INFORMATION_SCHEMA?

O banco de dados INFORMATION_SCHEMA é um conjunto de visualizações padrão ANSI que podemos encontrar no SQL Server, mas também no MySQL. Outros sistemas de banco de dados também têm um banco de dados exatamente igual ou semelhante implementado. Ele fornece o acesso somente leitura aos detalhes relacionados aos bancos de dados e seus objetos (tabelas, restrições, procedimentos, visualizações …) armazenados no servidor.

Você pode facilmente usar esses dados para:

  • Verifique o que está no servidor e / ou no banco de dados

  • Verifique se tudo está como o esperado (por exemplo, em comparação com a última vez que você executou essa verificação)

  • Automatize processos e crie algum código complexo (por exemplo, geradores de código – falaremos sobre isso mais tarde)

Portanto, esse banco de dados pode ser muito útil em alguns casos, principalmente se você estiver na função DBA

Listando todos os bancos de dados

Talvez a primeira coisa lógica a fazer é listar todos os bancos de dados que estão atualmente em nosso servidor. Nós podemos fazer isso de uma
algumas maneiras. Embora esses dois não estejam diretamente relacionados ao uso do INFORMATION_SCHEMA e sejam SQL
Específico ao servidor, vamos vê-los primeiro.

liste todos os bancos de dados no servidor - usando sys. ou sp

Você pode perceber facilmente que a primeira consulta retorna muito mais detalhes (várias colunas fora desta foto) do que a segunda consulta. Ele usa um objeto sys específico do SQL Server. Enquanto isso funciona muito bem, é muito específico, por isso vou entrar em detalhes em outro artigo. A segunda instrução é a execução do procedimento armazenado do sistema sp_databases que retorna as colunas predefinidas.

Usando o BUSINESS_SCHEMA para acessar os dados das tabelas

Como esse banco de dados é um padrão ANSI, as consultas a seguir também devem funcionar em outros sistemas DBMS. Listaremos todas as tabelas no banco de dados que selecionamos e também todas as restrições. Para fazer isso, usaremos as seguintes consultas:

listar todas as tabelas e restrições no banco de dados selecionado

A primeira coisa importante é que, após a palavra-chave USAR, devemos definir o banco de dados no qual queremos executar consultas.
O resultado é esperado. A primeira consulta lista todas as tabelas do nosso banco de dados, enquanto a segunda consulta retorna todas as restrições que definimos quando criamos nosso banco de dados. Em ambos, além do nome e do esquema do banco de dados ao qual pertencem, podemos ver muitos outros detalhes.

É importante notar que, nas restrições, também temos a coluna TABLE_NAME, que indica qual tabela é essa
restrição está relacionada a. Usaremos esse fato para relacionar as tabelas INFORMATION_SCHEMA.TABLES e INFORMATION_SCHEMA.TABLE_CONSTRAINTS para criar nossa consulta personalizada. Vamos dar uma olhada na consulta e na sua ”
resultado.

listar todas as chaves no banco de dados selecionado

Sem dúvida, esta consulta parece legal. Ainda assim, vamos comentar algumas coisas. Com esta consulta, temos:

  • Mais uma vez, apontamos qual banco de dados estamos usando. Isso poderia ter sido evitado se você escrever
    sempre antes de INFORMATION_SCHEMA, p.
    our_first_database.INFORMATION_SCHEMA.TABLES.TABLE_NAME. Eu não prefiro assim

  • Juntamos duas tabelas, da mesma maneira, juntamos duas tabelas de banco de dados “regulares”. É bom saber, mas,
    como veremos mais adiante, você pode associar muitas coisas (tabelas de sistema, subconsultas) e não apenas tabelas “regulares”

  • Também ordenamos nosso resultado para que possamos observar facilmente todas as restrições em cada tabela

Talvez você esteja se perguntando por que faria algo assim. Bem, com pequenas modificações nesta consulta, você pode contar facilmente um número de chaves em cada tabela. Vamos fazer isso.

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

o banco de dados INFORMATION_SCHEMA - conte as chaves nas tabelas

A partir do resultado, agora podemos observar facilmente o número de chaves / restrições em todas as tabelas. Desta forma, poderíamos encontrar
tabelas:

  • Sem uma chave primária. Isso pode ser o resultado de um erro no processo de design

  • Sem chaves estrangeiras. As tabelas sem chaves estrangeiras devem ser apenas dicionários ou algum tipo de tabela de relatório.
    Em todos os outros casos, devemos ter uma chave estrangeira

  • Embora UNIQUE não deva estar relacionado a erros, na maioria dos casos, podemos esperar que a tabela tenha apenas 0 ou 1
    Valores UNIQUE. Caso haja mais, poderíamos verificar por que isso

Consultas como essa podem fazer parte dos controles. Está tudo bem com seu banco de dados. Você poderia complicar
ainda mais e use essa consulta como uma subconsulta para uma consulta mais complexa que testará automaticamente predefinições
erros / alertas / avisos.

As tabelas de INFORMAÇÃO_SCHEMA

Seria difícil experimentar todas as tabelas e mostrar o que elas retornam. Pelo menos, isso seria difícil de colocar em um artigo legível. É altamente recomendável que você brinque com o banco de dados INFORMATION_SCHEMA e explore o que é onde. A única coisa que farei aqui é listar todas as tabelas (visualizações) que você tem à disposição. Eles são:

  • CHECK_CONSTRAINTS – detalhes relacionados a cada restrição CHECK

  • COLUMN_DOMAIN_USAGE – detalhes relacionados a colunas que possuem um tipo de dados de alias

  • COLUMN_PRIVILEGES – privilégios de colunas concedidos ou concedidos pelo usuário atual

  • COLUMNS – colunas do banco de dados atual

  • CONSTRAINT_COLUMN_USAGE – detalhes sobre restrições relacionadas à coluna

  • CONSTRAINT_TABLE_USAGE – detalhes sobre restrições relacionadas à tabela

  • DOMAIN_CONSTRAINTS – detalhes relacionados aos tipos de dados de alias e regras relacionadas a eles (acessíveis por este usuário)

  • DOMAINS – detalhes do tipo de dados de alias (acessíveis por este usuário)

  • KEY_COLUMN_USAGE – detalhes retornados se a coluna estiver relacionada com chaves ou não

  • PARÂMETROS – detalhes relacionados a cada parâmetro relacionado a funções e procedimentos definidos pelo usuário, acessíveis por
    esse usuário

  • REFERENTIAL_CONSTRAINTS – detalhes sobre chaves estrangeiras

  • ROTINAS – detalhes relacionados às rotinas (funções e procedimentos) armazenados no banco de dados

  • ROUTINE_COLUMNS – uma linha para cada coluna retornada pela função com valor de tabela

  • SCHEMATA – detalhes relacionados a esquemas no banco de dados atual

  • TABLE_CONSTRAINTS – detalhes relacionados às restrições de tabela no banco de dados atual

  • TABLE_PRIVILEGES – privilégios de tabela concedidos ou concedidos pelo usuário atual

  • TABLES – detalhes relacionados a tabelas armazenadas no banco de dados

  • VIEW_COLUMN_USAGE – detalhes sobre as colunas usadas na definição de exibição

  • VIEW_TABLE_USAGE – detalhes sobre as tabelas usadas na definição de exibição

  • VIEWS – detalhes relacionados às visualizações armazenadas no banco de dados

Conclusão

A consulta ao banco de dados INFORMATION_SCHEMA fornece várias opções sobre como controlar as alterações na estrutura do banco de dados, bem como implementar algum nível de automação na camada do banco de dados. Para conseguir esses dois, você deve seguir algumas regras, como convenção de nomenclatura, regras internas de modelagem de dados, etc. Você também pode explorá-lo para documentar seu banco de dados. Mais adiante nesta série, falaremos mais sobre isso.

Índice

Emil Drkusic

Emil Drkusic

Emil é um profissional de banco de dados com mais de 10 anos de experiência em tudo relacionado a bancos de dados. Durante os anos, ele trabalhou no setor de TI e finanças e agora trabalha como freelancer.

Seus compromissos no passado e no presente variam de design e codificação de banco de dados a ensino, consultoria e redação de bancos de dados. Também para não esquecer, BI, criando algoritmos, xadrez, filatelia, 2 cães, 2 gatos, 1 esposa, 1 bebê …

Você pode encontrá-lo no LinkedIn

Emil Drkusic

Últimas mensagens de Emil Drkusic (ver todos)

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br
Leia Também  Como reverter usando transações explícitas do SQL Server