Fazer update em outro banco de dados da mesma instância

As vezes, é necessário realizar operações de dados entre diferentes bancos de dados dentro de uma instância SQL. Na verdade, isso é muito comum. Então aqui vai a dica.

1ª opção: Buscando os dados de um outro banco de dados ao qual você está contectado:

  update ocrd
     set ocrd.cardname = ocrd2.cardname
    from sboatlantida..ocrd ocrd2
   where ocrd.cardcode = ocrd2.cardcode

Neste exemplo, especificamos a tabela que será atualizada logo após o update, um alias para a tabela do banco onde vamos pegar os dados (ocrd2) e na clausula where, fazemos a correlação entre as tabelas.

2ª opção: Realizando update em outro banco de dados:

  update ocrd1
     set ocrd1.cardname = ocrd2.cardname
    from atl..ocrd ocrd1
   inner join SBOAtlantida..ocrd ocrd2
      on ocrd1.cardcode = ocrd2.cardcode

Já este exemplo, estamos atualizando o banco de dados ATL através de dados provenientes do banco SBOAtlantida. Para iniciantes, pode parecer estranho especificar na clausula FROM (da onde) qual a tabela a ser atualizada. Mas entenda como a clausula FROM onde especificamos o nome das tabelas, independente da qual será atualizada. Este segundo modelo pode ser utilizado em ambos os casos descritos acima.

Muito útil quando precisa interligar bases de diferentes sistemas.

Tamanho de todas as tabelas de todos os bancos de dados de uma instância SQL Server

Caros colegas, hoje recebi um comentário que acabou se tornando uma sugestão bem interessante, visto que recebo muitas visitas sobre como obter o tamanho das tabelas de um banco. Bom, a solução que vou propor aqui vai mais a fundo, de forma a proporcionar uma ferramenta que facilite a gestão do tamanho de tabelas, quando isto é necessário. E é claro, eu preciso abrir 2013 com um post bacana, então aí vai!

É claro, como profissional da área, não posso negligenciar uma verdade: se você está tendo que monitorar tamanhos de tabelas no banco de dados, existem problemas bem mais graves por trás de tudo isso. Ficar resolvendo somente o resultado de um problema não resolve o problema em si, tem que atacar a causa, sempre. Nem preciso entrar em detalhes, certo? Mas se você não consegue resolver a fonte do problema (ex.: sistema de terceiros), essa dica vai facilitar sua vida.

Hoje vou postar duas alternativas bem simples para melhorar a utilização do cáculo, uma derivada da outra. A primeira procedure, que eu acho a mais interessante, realizará o envio automático ao seu e-mail com um relatório em HTML com as maiores tabelas, de todos os bancos de dados, sendo acionada por uma JOB, por exemplo. Eu já fiz uma formatação HTML que permita visualizar em dispositivos móveis (claro, qualquer aparelho que reproduza ao menos HTML, por favor). A segunda é o mesmo cálculo, porém uma procedure simplificada, para ser executada manualmente e que retorna uma tabela com todos os registros por linha, para visualizar no resultado da query, ou copiar e colar para uma planilha, por exemplo.

* Importante

- ambas as procedures podem ser salvas e executas em qualquer banco de dados, isso é indiferente.

- Para a utilização do primeiro modelo, será necessário conhecimento em JOBS (criar job) e DATABASE MAIL (serviço ativo e conta SMTP devidamente configurada).

Então vamos começar pelo procedimento que envia o relatório por email. Observe os comentários no código para melhores orientações sobre como personalizar a execução de acordo com suas necessidades:

-- exec sp_espacotabelabanco_email
create proc sp_espacotabelabanco_email
as
 
/*
  Fabiano Cores 02.01.2013
  http://sqlburger.blogspot.com
  Segunda versão do cálculo do tamanho das tabelas, que trata todos os bancos da instância onde 
  for executada.
  
  Versão 1.: Envio do relatório para um email pré-determinado
  
*/
 
  set nocount on

  declare @vname   varchar(100)
  declare @vbase   varchar(100)
  declare @sql     varchar(max)
  declare @email   varchar(max)
  declare @assunto varchar(200)
  declare @vtexto  varchar(max)
  
  create table #tmpTamTabela (
    db         varchar(100) null
  , name       varchar(100) null
  , rows       int          null
  , reserved   varchar(25)  null
  , data       varchar(25)  null
  , index_size varchar(25)  null
  , unused     varchar(25)  null )
  
  create table #tmpTabelas (
    tabela varchar(100) null
  )
  
  create table #retorno (
    texto varchar(max) not null
  , ordem int identity
  )
  
  insert into #retorno (texto)
    select '<table border=1 cellpadding=2 cellspacing=0 width=100%>'

  insert into #retorno (texto)
    select '<tr bgcolor=#000000>'
         + '  <td align=left  Valign=top><font face=tahoma size=2 color=#FFFFFF><b>Tabela</b></font></td>'
         + '  <td align=right Valign=top><font face=tahoma size=2 color=#FFFFFF><b>Linhas</b></font></td>'
         + '  <td align=right Valign=top><font face=tahoma size=2 color=#FFFFFF><b>Tamanho</b></font></td>'
         + '  <td align=right Valign=top><font face=tahoma size=2 color=#FFFFFF><b>Dados</b></font></td>'
         + '  <td align=right Valign=top><font face=tahoma size=2 color=#FFFFFF><b>Index</b></font></td>'
         + '  <td align=right Valign=top><font face=tahoma size=2 color=#FFFFFF><b>Livre</b></font></td>'
         + '</tr>'
             
  declare cp0 cursor local fast_forward read_only for
    select name
     from master..sysdatabases
    /* ESPECIFIQUE AQUI AS EXCEÇÕES PARA CÁLCULO */ 
    where name not in ('master', 'tempdb', 'model', 'msdb')
    order by name
  
  open cp0
  
  while 0 =0 
  begin
    fetch next from cp0 into @vbase
    if @@fetch_status <> 0 break
    
    truncate table #tmpTabelas
    
    insert into #retorno (texto)
      select '<tr bgcolor=#900000><td colspan=6><font face=tahoma size=2 color=#ffffff>DB: <b>' + rtrim(@vbase) + '</b></td></tr>'
    
    select @sql = '
        
          insert into #tmpTabelas (tabela)
            select name
              from [' + rtrim(@vbase) + ']..sysobjects
             where type = ''U''
            
            '
    
    exec(@sql)
      
    declare cp1 cursor local fast_forward read_only for
      select tabela from #tmpTabelas
       order by 1
   
    open cp1
   
    while 1 = 1
    begin
      fetch next from cp1 into @vname
      if @@fetch_status <> 0 break
      
      select @sql = '
        
          insert into #tmpTamTabela (name, rows, reserved, data, index_size, unused)
            exec [' + rtrim(@vbase) + ']..sp_spaceused ''' + rtrim(@vname) + '''
            
          update #tmpTamTabela
             set db = ''' + rtrim(@vbase) + '''
           where db is null
            
            '
      
      exec(@sql)
      
    end
    close cp1
    deallocate cp1    
    
    insert into #retorno (texto)
      /* altere aqui a quantidade de tabelas a ser exibida no relatório */
      select top 10 '<tr>'
           + '  <td align=left Valign=top><font face=tahoma size=1>' + rtrim(name) + '</font></td>'
           + '  <td align=right Valign=top><font face=tahoma size=1>' + rtrim(rows) + '</font></td>'
           + '  <td align=right Valign=top><font face=tahoma size=1>' + rtrim(reserved) + '</font></td>'
           + '  <td align=right Valign=top><font face=tahoma size=1>' + rtrim(data) + '</font></td>'
           + '  <td align=right Valign=top><font face=tahoma size=1>' + rtrim(index_size) + '</font></td>'
           + '  <td align=right Valign=top><font face=tahoma size=1>' + rtrim(unused) + '</font></td>'
           + '</tr>'
        from #tmpTamTabela
       where db = @vbase
       order by convert(int, replace(reserved, ' KB','')) desc  

  end
  close cp0
  deallocate cp0
  
  insert into #retorno (texto)
    select '</table>'

  declare cp2 cursor local fast_forward read_only for
    select texto from #retorno order by ordem
    
  open cp2
  
  while 1 = 1
  begin
    fetch next from cp2 into @vtexto
    if @@fetch_status <> 0 break
    
    select @email = isnull(@email, '') + rtrim(@vtexto)
    
  end
  close cp2
  deallocate cp2
  
  set @assunto = 'Relatório tabelas - ' + rtrim(@@SERVERNAME)
  
  exec msdb.dbo.sp_send_dbmail @profile_name           =  'atlanta',
                               @recipients             =  'fbcores@gmail.com',
                               @copy_recipients        =  'fabiano.cores@nms.com.br', 
                               @blind_copy_recipients  =  '', 
                               @subject                =  @assunto,
                               @body                   =  @email,
                               @body_format            =  'HTML'

 

Não vou entrar em detalhes em como se utiliza a função de email ou como se cria uma JOB no SQL. Creio que sejam assuntos para outro artigo. Existem muito conteúdo explicativo sobre como ativar e utilizar o DATABASE MAIL e o SQL SERVER AGENT, basta dar uma procurada na internet. Mas se tiverem dificuldades em concluir essa configuração, me avisem pra eu saber se é necessário ou não fazer um artigo específico para ensinar como utilizar essas ferramentas.

A imagem abaixo é a visualização do relatório no meu tablet. No celular também ficou bem legal:

 

O segundo exemplo, é uma versão simplificada do procedimento, que não trata a formatação de texto nem nada parecido. É uma procedure para ser executada manualmente sempre que o administardor quiser consultar os tamanhos das tabelas:

-- exec sp_espacotabelabanco
create proc sp_espacotabelabanco
as
 
/*
  Fabiano Cores 02.01.2013
  http://sqlburger.blogspot.com
  Segunda versão do cálculo do tamanho das tabelas, que trata todos os bancos da instância onde 
  for executada.
  
  Versão 2: simplificada, para execução manual
  
*/
 
  set nocount on

  declare @vname   varchar(100)
  declare @vbase   varchar(100)
  declare @sql     varchar(max)
  
  create table #tmpTamTabela (
    db         varchar(100) null
  , name       varchar(100) null
  , rows       int          null
  , reserved   varchar(25)  null
  , data       varchar(25)  null
  , index_size varchar(25)  null
  , unused     varchar(25)  null )
  
  create table #tmpTabelas (
    tabela varchar(100) null
  )
  
  declare cp0 cursor local fast_forward read_only for
    select name
     from master..sysdatabases
    /* ESPECIFIQUE AQUI AS EXCEÇÕES PARA CÁLCULO */ 
    where name not in ('master', 'tempdb', 'model', 'msdb')
    order by name
  
  open cp0
  
  while 0 =0 
  begin
    fetch next from cp0 into @vbase
    if @@fetch_status <> 0 break
    
    truncate table #tmpTabelas
    
    select @sql = '
        
          insert into #tmpTabelas (tabela)
            select name
              from [' + rtrim(@vbase) + ']..sysobjects
             where type = ''U''
            
            '
    
    exec(@sql)

    declare cp1 cursor local fast_forward read_only for
      select tabela from #tmpTabelas
       order by 1
   
    open cp1
   
    while 1 = 1
    begin
      fetch next from cp1 into @vname
      if @@fetch_status <> 0 break
      
      select @sql = '
        
          insert into #tmpTamTabela (name, rows, reserved, data, index_size, unused)
            exec [' + rtrim(@vbase) + ']..sp_spaceused ''' + rtrim(@vname) + '''
            
          update #tmpTamTabela
             set db = ''' + rtrim(@vbase) + '''
           where db is null
            
            '
      
      exec(@sql)
      
    end
    close cp1
    deallocate cp1    
    
  end
  close cp0
  deallocate cp0
  
  select db as 'Banco'
       , name as 'Nome'
       , rows as 'Linhas'
       , convert(int, replace(reserved, ' KB','')) as 'Tamanho total'
       , convert(int, replace(data, ' KB',''))as 'Dados'
       , convert(int, replace(index_size, ' KB',''))as 'Index'
       , convert(int, replace(unused, ' KB',''))as 'Não utilizado'
    from #tmpTamTabela
   order by db, convert(int, replace(reserved, ' KB','')) desc  

 

Esta procedure acima retorna o resultado da seguinte forma:

 

Infelizmente não estou tendo tempo para escrever mais artigos, mas espero ter ajudado com este, tentei simplificar o máximo possível, só copiar, colar e desfrutar.

E feliz 2013!