Com a ascensão dos sistemas personalizados para cada situação de um ambiente de trabalho/negócio, as informações geradas pelos menos tem a necessidade de serem armazenadas para serem consultadas num futuro breve, fazer análises baseadas em um conjunto de outras informações afim de obter um resultado, entre outras situações. Para o armazenamento dessas informações em banco de dados são criadas estruturas em forma de tabelas, contendo colunas e linhas que classificam a informação de forma categorizada e organizada. Porém, para o usuário final, o banco de dados deve abstrair a forma pela qual as informações são organizadas, garantindo apenas que as informações estão salvas e a um fácil acesso. Contudo, no dia a dia de um programador surgem necessidades de otimizar algumas etapas no decorrer do desenvolvimento de software, como por exemplo, na construção de um framework, e para isso é preciso criar algumas rotinas que nos auxiliem em determinadas ocasiões.

Partindo desta ideia, iremos criar um programa Windows Forms em C# que disponibilize essas informações acessando Views existentes no banco de dados SQL Server, onde são disponibilizadas diversas informações sobre objetos presentes no banco, como mostra a Tabela 1.

Views Descrição
CHECK_CONSTRAINT Verifica restrições
COLUMN_PREVILEGES Colunas com privilégios garantidos para ou pelo usuário atual
COLUMNS Todas as colunas
CONSTRAINT_TABLE_USAGE Tabelas que possuem uma restrição definida
DOMAINS Todos os tipos de dados definidos pelo usuário
KEY_COLUMN_USAGE Colunas restringidas como uma chave
PARAMETERS Todos os parâmetros para funções definidas pelo usuário e storedprocedures
ROUTINES Todas as funções definidas pelo usuário e storedprocedures
SCHEMATA Todos os bancos de dados
TABLE_CONSTRAINTS Todas as restrições das tabelas
TABLE_PRIVILEGES Tabelas com privilégio garantido para ou pelo usuário atual
TABLES Todas as tabelas
VIEWS_TABLE_USAGE Tabelas usadas em uma View
VIEWS Todas as Views
Tabela 1. Views disponibilizadas pelo SQL Server

Para esse artigo usaremos as seguintes views:

  • INFORMATION_SCHEMA.TABLES – Retorna uma linha para cada tabela presente no BD, lembrando que apenas serão listadas as tabelas que o usuário tem permissão. Esta view retorna as informações presentes na Tabela 2.
    Nome Descrição Tipo
    TABLE_CATALOG Qualificador da tabela nvarchar(128)
    TABLE_SCHEMA Nome do schema que a tabela pertence nvarchar(128)
    TABLE_NAME Nome da tabela sysname
    TABLE_TYPE Tipo da tabela, view ou table varchar(10)
    Tabela 2. Informações da View INFORMATION_SCHEMA.TABLES
  • INFORMATION_SCHEMA.COLUMNS – Retorna uma linha para cada coluna que pode ser acessada pelo usuário em uma determinada tabela do banco de dados. As informações disponibilizadas são as presentes na Tabela 3.
    Nome Descrição Tipo
    TABLE_CATALOG Qualificador da tabela nvarchar(128)
    TABLE_SCHEMA Nome do schema que a tabela pertence nvarchar(128)
    TABLE_NAME Nome da tabela nvarchar(128)
    COLUMN_NAME Nome da coluna nvarchar(128)
    ORDINAL_POSITION Número de identificação da coluna int
    COLUMN_DEFAULT Valor padrão da coluna nvarchar(4000)
    IS_NULLABLE Informa se a coluna permite NULO varchar(3)
    DATA_TYPE Tipo do dado nvarchar(128)
    CHARACTER_MAXIMUM_LENGTH Valor máximo em caracteres int
    CHARACTER_OCTET_LENGTH Comprimento máximo em caracteres int
    NUMERIC_PRECISION Precisão numérica dos dados tinyint
    NUMERIC_PRECISION_RADIX Precisão da raiz dos dados smallint
    NUMERIC_SCALE Escala aproximada de dados numéricos int
    DATETIME_PRECISION Tipo de datetime usado smallint
    CHARACTER_SET_CATALOG Indica em que banco de dados o conjunto de caracteres está alocado nvarchar(128)
    CHARACTER_SET_SCHEMA Sempre retorna NULO nvarchar(128)
    CHARACTER_SET_NAME Retorna o nome exclusivo para o conjunto de caracteres. nvarchar (128)
    COLLATION_CATALOG Sempre retorna NULO nvarchar(128)
    COLLATION_SCHEMA Sempre retorna NULO nvarchar(128)
    COLLATION_NAME Retorna o nome exclusivo se o tipo da coluna for texto ou conjunto de caracteres nvarchar(128)
    DOMAIN_CATALOG Se a coluna é um alias nvarchar(128)
    DOMAIN_SCHEMA Se a coluna é um tipo de dados definido pelo usuário nvarchar(128)
    DOMAIN_NAME Nome do tipo de dados definido pelo usuário nvarchar(128)
    Tabela 3. Informações da View INFORMATION_SCHEMA.COLUMNS
  • INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE – Retorna uma linha para cada coluna que tenha uma restrição definida nas tabelas do banco de dados em que o usuário tenha permissão. Vejamos as informações disponibilizadas por essa View na Tabela 4.
    Nome Descrição Tipo
    TABLE_CATALOG Qualificador da tabela nvarchar(128)
    TABLE_SCHEMA Nome do schema de contem a coluna da tabela nvarchar(128)
    TABLE_NAME Nome da tabela nvarchar(128)
    COLUMN_NAME Nome da coluna nvarchar(128)
    CONSTRAINT_CATALOG Qualificador da restrição nvarchar(128)
    CONSTRAINT_SCHEMA Nome do schema que contém a restrição definida nvarchar(128)
    CONSTRAINT_NAME Nome da restrição definida nvarchar(128)
    Tabela 4. Informações da View INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
  • INFORMATION_SCHEMA.KEY_COLUMN_USAGE – Retorna uma linha para cada coluna que representa uma chave de uma determinada tabela de um banco de dados especifico para que o usuário tenha permissão. Esta View retorna as informações presentes na Tabela 5.
    Nome Descrição Tipo
    CONSTRAINT_CATALOG Qualificador da restrição nvarchar(128)
    CONSTRAINT_SCHEMA Nome do schema que contém a restrição definida nvarchar(128)
    CONSTRAINT_NAME Nome da restrição nvarchar(128)
    TABLE_CATALOG Qualificador da tabela nvarchar(128)
    TABLE_SCHEMA Nome do schema da tabela nvarchar(128)
    TABLE_NAME Nome da tabela que contem a restrição nvarchar(128)
    COLUMN_NAME Nome da coluna que contem a restrição nvarchar(128)
    ORDINAL_POSITION Número de identificação da coluna int
    Tabela 5. Informações da View INFORMATION_SCHEMA.KEY_COLUMN_USAGE

    Criando um cenário de teste

    Agora que já conhecemos as Views que fornecem informações sobre objetos do banco de dados, vamos criar um cenário para que possamos obter as informações dele. Para isso, utilizaremos um banco de dados SQL Server com o nome dbTeste e criaremos duas tabelas: uma de funcionário e outra de produto. Após isso criaremos uma aplicação .NET para que possamos obtê-las.

    Vamos começar pelo banco de dados criando a tabela de produto com a execução do script da Listagem 1 e teremos as colunas presentes na Tabela 6.

    Nome Descrição Tipo
    prd_id Identificador do produto int
    prd_cod Código do produto int
    prd_descricao Descrição do produto nvarchar(250)
    prd_abreviacao Abreviação do produto nvarchar(100)
    prd_tipo Tipo do produto char(2)
    prd_dat_cad Data de cadastro do produto datetime
    prd_valor Valor do produto numeric(9, 2)
    Tabela 6. Descrição das colunas da tabela de produto
    
    USE [dbTeste]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[produto](
    	[prd_id] [int] IDENTITY(1,1) NOT NULL,
    	[prd_cod] [int] NOT NULL,
    	[prd_descricao] [nvarchar](250) NOT NULL,
    	[prd_abreviacao] [nvarchar](100) NOT NULL,
    	[prd_tipo] [char](2) NOT NULL,
    	[prd_dat_cad] [datetime] NOT NULL,
    	[prd_valor] [numeric](9, 2) NOT NULL,
    CONSTRAINT [PK_produto] PRIMARY KEY CLUSTERED 
    (
              [prd_id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = 
    OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  
    = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    Listagem 1. Script da tabela produtos

    Agora iremos criar a tabela com o nome funcionario com o script da Listagem 2, e com as colunas da Tabela 7.

    Nome Descrição Tipo
    fun_id Identificador do funcionário int
    fun_mat Matrícula do funcionário varchar(10)
    fun_nome Nome do funcionário nvarchar(250)
    fun_sexo Sexo do funcionário char(1)
    fun_dat_nasc Data de nascimento do funcionário datetime
    fun_ci Identidade do funcionário bigint
    fun_email Email do funcionário varchar(100)
    fun_telefone Telefone do funcionário varchar(11)
    Tabela 7. Descrição das colunas da tabela de funcionário
    
    USE [dbTeste]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[funcionario](
    	[fun_id] [int] IDENTITY(1,1) NOT NULL,
    	[fun_mat] [varchar](10) NOT NULL,
    	[fun_nome] [varchar](250) NOT NULL,
    	[fun_sexo] [char](1) NOT NULL,
    	[fun_dat_nasc] [datetime] NOT NULL,
    	[fun_ci] [bigint] NOT NULL,
    	[fun_email] [varchar](100) NULL,
    	[fun_telefone] [varchar](11) NULL,
    CONSTRAINT [PK_funcionario_1] PRIMARY KEY CLUSTERED 
    (
              [fun_id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = 
    OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = 
    ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    Listagem 2. Tabela Funcionario

    Em seguida execute um select utilizando as Views que foram apresentadas no início do artigo, como mostra a Listagem 3.

    
    SELECT * 
    	FROM INFORMATION_SCHEMA.TABLES t
    	          JOIN INFORMATION_SCHEMA.COLUMNS C ON C.TABLE_NAME = T.TABLE_NAME
    	WHERE T.TABLE_NAME = 'funcionario'
    	ORDER BY C.ORDINAL_POSITION
    Listagem 3. Consulta a tabela funcionario

    Com esse select é possível obter todas as colunas com suas respectivas informações da tabela de funcionário.

    Vamos agora criar um projeto do tipo Windows Forms utilizando a linguagem C#. Para isso, abra o Visual Studio e acesse ao menu File > New > Project. Selecionamos a linguagem Visual C# e em seguida selecionamos o tipo Windows Forms Application, dando um nome ao projeto, como mostra a Figura 1.

    Novo projeto do tipo Windows Forms
    Figura 1. Novo projeto do tipo Windows Forms

    Com o projeto criado adicionaremos uma referência para que possamos utilizar nossa ConnectionString no App.config da aplicação. Para isso, clicamos com o botão direito do mouse sobre o item Reference na Solution Explorer e clicamos em Add Reference. Com a janela nova aberta procuraremos a referência System.Configuration na aba de Framework e adicionamos ao projeto. Em seguida vamos para o arquivo de configuração e vamos inserir nossa ConnectionString dentro das tags de , como mostra a Listagem 4.

    
    <connectionStrings>
      <add name="TableInformationCS" 
      connectionString="Data Source=.\SQLEXPRESS;Initial 
      Catalog=dbTeste;Integrated Security=True"
        providerName="System.Data.SqlClient" />
    </connectionStrings>
    Listagem 4. ConnectionString

    Ao adicionar uma nova string de conexão, abrimos uma tag dentro das tags e primeiro informamos o nome da nossa string de conexão através da propriedade name, pois esse será com esse nome que poderemos ter acesso às informações de conexão com nosso banco de dados. Depois temos a propriedade connectionString onde nela serão adicionadas todas as informações de conexão necessárias com nosso banco de dados. Em nosso caso temos o Data Source que é composto pelo servidor\instância do banco de dados, ou seja, representamos o servidor local através do “.” e a instância do nosso banco por SQLEXPRESS. Após isso, temos o “Initial Catalog” que contém o nome do banco de dados a ser acessado, que é o dbTeste. Por último, temos o “Integrated Security” que informa se a conexão será setada através de um Boolean, True ou False. A propriedade providerName conterá o “provider” a ser utilizado na comunicação com o banco de dados, e já que estamos usando o SQL Server, o provider será o System.Data.SqlClient.

    Para que possamos utilizar os dados da nossa string de conexão que criamos, vamos desenvolver uma classe para conexão com o banco. Clicando com o botão direito do mouse sobre nosso projeto, acessamos o menu Add > Class e criaremos uma classe com o nome DbUtil. Dentro desta classe conterá métodos para obter, inserir, alterar e excluir dados do nosso banco, como mostra a Listagem 5.

    
    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace TableInformation
    {
      public class DbUtil
      {
          #region Objetos Estáticos
          // Objeto Connection para obter acesso ao SQL Server 
          public static SqlConnection sqlconnection = 
          new SqlConnection();
          // Objeto SqlCommand para executar os com 
          public static SqlCommand comando = new SqlCommand();
          // Objeto SqlParameter para adicionar os 
          parâmetros necessários em nossas consultas 
          public static SqlParameter parametro = new SqlParameter();
          #endregion
    
          #region Obter SqlConnection
          public static SqlConnection connection()
          {
              try
              {
                  // Obtemos os dados da conexão existentes no 
                  WebConfig utilizando o ConfigurationManager 
                  string dadosConexao = ConfigurationManager
                  .ConnectionStrings["TableInformationCS"].ConnectionString;
                  // Instanciando o objeto 
                  SqlConnection sqlconnection = new SqlConnection(dadosConexao);
                  //Verifica se a conexão esta fechada. 
                  if (sqlconnection.State == ConnectionState.Closed)
                  {
                      //Abre a conexão. 
                      sqlconnection.Open();
                  }
                  //Retorna o sqlconnection. 
                  return sqlconnection;
              }
              catch (SqlException ex)
              {
                  throw ex;
              }
          }
          #endregion
    
          #region Abre Conexão
          public void Open()
          {
              sqlconnection.Open();
          }
          #endregion
    
          #region Fecha Conexão
          public void Close()
          {
              sqlconnection.Close();
          }
          #endregion
    
          #region Adiciona Parâmetros
          public void AdicionarParametro(string nome, SqlDbType 
          tipo, int tamanho, object valor)
          {
              // Cria a instância do Parâmetro e 
              adiciona os valores 
              parametro = new SqlParameter();
              parametro.ParameterName = nome;
              parametro.SqlDbType = tipo;
              parametro.Size = tamanho;
              parametro.Value = valor;
              // Adiciona ao comando SQL o parâmetro 
              comando.Parameters.Add(parametro);
          }
          #endregion
    
          #region Adiciona Parâmetros
          public void AdicionarParametro(string nome, 
          SqlDbType tipo, object valor)
          {
              // Cria a instância do Parâmetro e adiciona os valores 
              SqlParameter parametro = new SqlParameter();
              parametro.ParameterName = nome;
              parametro.SqlDbType = tipo;
              parametro.Value = valor;
              // Adiciona ao comando SQL o parâmetro 
              comando.Parameters.Add(parametro);
          }
          #endregion
    
          #region Remove os parâmetros
          public void RemoverParametro(string pNome)
          {
              // Verifica se existe o parâmetro 
              if (comando.Parameters.Contains(pNome))
                  // Se exite remove o mesmo 
                  comando.Parameters.Remove(pNome);
          }
          #endregion
    
          #region Limpar Parâmetros
          public void LimparParametros()
          {
              comando.Parameters.Clear();
          }
          #endregion
    
          #region Executar Consulta SQL
          public DataTable ExecutaConsulta(string sql)
          {
              try
              {
                  // Pega conexão com a base SQL Server 
                  comando.Connection = connection();
                  // Adiciona a instrução SQL 
                  comando.CommandText = sql;
                  //Executa a query sql. 
                  comando.ExecuteScalar();
                  // Ler os dados e passa para um DataTable 
                  IDataReader dtreader = comando.ExecuteReader();
                  DataTable dtresult = new DataTable();
                  dtresult.Load(dtreader);
                  // Fecha a conexão 
                  sqlconnection.Close();
                  // Retorna o DataTable com os dados da consulta 
                  return dtresult;
              }
              catch (Exception ex)
              {
                  // Retorna uma exceção simples que pode ser
                   tratada por parte do desenvolvedor 
                  // Exemplo: if (ex.Message.toString()
                  .Contains(‘Networkig’)) 
                  // Exemplo throw new 
                  Exception(‘Problema de rede detectado’); 
                  throw ex;
              }
          }
          #endregion
    
          #region Executa uma instrução SQL: INSERT, UPDATE e DELETE
          public int ExecutaAtualizacao(string sql)
          {
              try
              {
                  //Instância o sqlcommand com a query sql que será 
                  executada e a conexão. 
                  comando = new SqlCommand(sql, connection());
                  comando.Connection = connection();
                  comando.CommandText = sql;
                  //Executa a query sql. 
                  int result = comando.ExecuteNonQuery();
                  sqlconnection.Close();
                  // Retorna a quantidade de linhas afetadas 
                  return result;
              }
              catch (Exception ex)
              {
                  // Retorna uma exceção simples que pode ser 
                  tratada por parte do desenvolvedor 
                  throw ex;
              }
          }
          #endregion
      }
    }
    Listagem 5. Classe de conexão com o banco de dados

    Precisamos agora criar um layout para nossa aplicação fazer a comunicação com o banco e obter as informações necessárias. Automaticamente, ao criarmos nosso projeto, o Visual Studio cria um formulário chamado Form1 e utilizaremos este no nosso caso de uso. Adicionaremos um “Button” para obter as tabelas do nosso banco de dados e um DataGridView para exibi-las. Em seguida, adicionaremos outro Button e DataGridView para obter as informações das colunas, como mostra a Figura 2.

    Layout do formulário da aplicação
    Figura 2. Layout do formulário da aplicação

    Implementaremos agora o evento Click do Button de obter tabelas. Para isso daremos um duplo clique sobre o Button desejado e automaticamente será gerado um evento no CodeBehind para este botão, chamado btnTables_Click. Adicione ao evento o código da Listagem 6.

    
    private void btnTables_Click(object sender, EventArgs e)
    {
      DbUtil dbUtil = new DbUtil();
      dbUtil.LimparParametros();
    
      string sql_tables = @"
          SELECT TABLE_SCHEMA AS 'SCHEMA', TABLE_NAME AS 'TABELA' 
    	      FROM INFORMATION_SCHEMA.TABLES 
    	      WHERE TABLE_TYPE = 'BASE TABLE' 
    	      ORDER BY TABLE_TYPE";
              
      DataTable dtTabelas = dbUtil.ExecutaConsulta(sql_tables);
      dgvTables.DataSource = dtTabelas;
    }
    Listagem 6. Evento onclick do button

    Instanciamos nossa classe de acesso a dados, limpamos os parâmetros e criamos o “Select” com a View INFORMATION_SCHEMA.TABLES para obter as informações sobre as tabelas presentes em nosso banco. Criamos um DataTable para guardar a informação que obtivemos através do método ExecutaConsulta da nossa classe de acesso. Por fim, associamos nosso DataTable ao DataSource do DataGridView para que a informação possa ser disponibilizada para o usuário.

    Agora implementaremos o evento click do outro botão que obterá as informações das colunas da tabela selecionada no DataGridView anterior, como mostra a Listagem 7.

    
    private void btnColumns_Click(object sender, EventArgs e)
    {
      DbUtil dbUtil = new DbUtil();
      dbUtil.LimparParametros();
    
      string sql_columns = @"
          SELECT COLUMN_NAME AS 'COLUNA', DATA_TYPE AS 'TIPO', 
                          IS_NULLABLE AS 'NULO' 
                      FROM INFORMATION_SCHEMA.COLUMNS
                      WHERE TABLE_NAME = @P_TABLE_NAME
                      ORDER BY ORDINAL_POSITION";
    
      dbUtil.AdicionarParametro("P_TABLE_NAME", SqlDbType.VarChar, 
      dgvTables.SelectedRows[0].Cells[1].Value);
      DataTable dtColumns = dbUtil.ExecutaConsulta(sql_columns);
      dgvColumns.DataSource = dtColumns;
    }
    Listagem 7. Evento onclick do segundo button

    Instanciamos novamente nossa classe de acesso e limpamos os parâmetros. Além disso, montamos um Select para obter as colunas de uma determinada tabela, mas dessa vez utilizando a View INFORMATION_SCHEMA.COLUMNS. Note que passamos um parâmetro no nosso comando select contendo o nome da tabela que desejamos obter as colunas. Criamos um parâmetro contendo a informação que será filtrada no nosso Select e chamamos o método ExecutaConsulta, guardando as informações obtidas em um DataTable. Para finalizar, associamos nosso DataTable ao DataSource do DataGridView para que o usuário possa visualizar as informações da tabela selecionada no DataGridView anterior.

    Neste momento já temos nossa aplicação compilando e pronta para ser executada ao apertar o botão F5. Com a janela aberta, vamos clicar no botão “Obter Tabelas” e o DataGridView irá exibir todas as tabelas do nosso banco de dados, como mostra a Figura 3.

    Exibindo todas as tabelas do banco de dados
    Figura 3. Exibindo todas as tabelas do banco de dados

    Selecionando uma determinada tabela e clicando em Obter Colunas, o DataGridView inferior listará todas as colunas pertencentes a tabela selecionada, como mostra a Figura 4.

    Exibindo informações da tabela selecionada
    Figura 4. Exibindo informações da tabela selecionada

    Podemos também utilizar outras Views disponibilizadas para obter informações mais especificas, ou caso já tenha um banco de dados configurado em sua máquina, basta abrir o arquivo App.config contido na solução e mudar o nome do banco de dados na propriedade Initial Catalog dentro das tags e colocar o nome do banco de dados desejado.