Se tem uma coisa que eu fiz muito ao longo de anos como DBA SQL, foi mexer com procedure, view, etc. Desde os meus primeiros contatos com SQL Server, eu aprendi a criar procedures e, mais tarde, outros tipos de objetos, como funções e/ou triggers… objetos que tem um código que fica salvo ali no banco. E uma ação muito comum para quem mexe com isso é lidar frequentemente com o código, seja para alterar ou apenas ler para entender ou confirmar algum comportamento. Há quem prefira usar o SSMS, navegar na estrutura hierárquica, clicar com o botão direito e mandar abrir. E, assim como esse que vos fala, há esses que preferem ali direto da janela de código mandar exibir o texto com a famosa sp_helptext (que há 12 anos foi um dos primeiros posts nesse blog), que eu acho muito mais rápido do que usar a interface.
A sp_helptext originalmente, retorna o código como 1 coluna em várias linhas, onde cada linha é uma linha do código fonte. Se você copia diretamente e joga no SSMS, você perde formatação. Para contornar isso, você pode ativar o modo texto no SSMS (atalho CTRL + T) e voi lá: Sua proc, por mais complexa que seja , retorna com a formatação correta e muito mais fácil de copiar… Aparentemente… Pois, em alguns casos, as linhas podem estar quebradas no lugar errado e o cabeçalho da coluna Text, vem junto… Quase bom, mas em 99% dos casos, ela segue sendo bem útil e acho melhor do que ir procurar na interface. Mas, as limitações não param por aí:
- eu preciso saber exatamente o que estou buscando. Se eu sei apenas parte do nome do objeto, preciso ir lá na sys.objects ou sys.procedures, achar o nome exato e então chamar a sp_helptext, ou usar uma função, tipo a OBJECT_DEFINITION.
- Outra dor é que ela não descriptografa sozinho, mesmo se for sysadmin, mesmo se eu tiver conectado como DAC
- E, se eu quiser Triggers DDL, trigger de logon? esquece, a sp_helptext não acha…
É aqui que eu vos apresento uma evolução depois de tanto usar a sp_helptext na minha vida: sp_showcode
Como já falei demais até aqui, vamos há algumas comparações para você entender que ganhos essa nova proc que criei pode trazer no seu dia a dia.
Saída e Formatação
Com a sp_helptext, se eu mandar exibir o código da procedure MinhaProc, ele vai exibir assim por padrão:
sp_helptext MinhaProc
Note que é uma tabela com 1 coluna chamada Text, e o resultado. Se eu quiser exibir como texto, eu aperto a opção no SSMS (ou uso o atalho CTRL + T) e:
Assim, a fomatação é mantida, mas nem tudo são flores. Esse é um caso mais bizarro, usando a stpPowerAlert_Log_Full, uma procedure padrão do Power Alerts que alerta sobre o uso de logs:
A sp_showcode traz várias melhorias aqui. A primeira é que você não precisa ativar o modo texto do SSMS:
E, ela mantém a formatação idêntica:
Note que ele já joga tudo ali fácil para você copiar. Apesar da grande maioria dos casos você não ter problemas com essa saída, ela tem apenas uma desvantagem: o tamanho máximo da linha é de 4000 caracteres (a sp_helptext tenta justamente contornar esse problema, mas piora a situação rs). Se você tiver um objeto com código que tenha alguma linha com mais de 4000 caracteres, procure um médico, então, a sp_showcode vai jogar pra linha seguinte, e aí ficar diferente do original. Mas, ao longo de 10 anos como DBA, eu acho que nunca vi código com uma linha tão grande assim, nem mesmos as procs internas criadas pela Microsoft… e cá entre nós, se alguém entupiu mais de 4000 caracteres em uma única linha, é porque não quer que você leia fácil essa proc rs!
Mas, se ainda sim você não quiser a saída em texto para estes casos, pode trocar pra XML:
sp_showcode MinhaProc,'xml'
Clicando na coluna ObjectDefinition retornada, uma nova aba é aberta:
Nesse modo você só vai ter problemas se usar algum caracter inválido XML. A proc tenta subsituir os inválidos mais prováveis por ?, mas pode ter algum que não mapeamos. Mas, geralmente, esse caracteres são de controle, invisíveis, então, dificilmente você usará isso na proc. Caso encontre esse problema, e, se for possível, reporte o caracter inválido como uma issue, para pensarmos em melhorias. De novo: esse é um problema que só vai acontecer com a saída XML.
Nome do objeto: Procure por uma parte
É bem comum você mexer com várias procedures em algum projeto como em prcListarVendas, prcCadastraVenda, etc. As vezes você não lembra o nome exato (por mexer com tantas) e precisa consultar antes de usar a sp_helptext, que aceita somente o nome completo. Com a sp_showcode, você pode usar o % para fazer um LIKE:
sp_showcode '%Venda%'
Quando você especifica o % e a sp_showcode encontra mais de um opção, ela entende que você não sabe o que quer, e apenas te mostra as opções. Você pode copiar o nome certo ou mudar alguns parâmetros que alterem esse comportamento. Isso é para evitar ler o código de vários objetos, que pode ser muita cosia e causar algum impacto no servidor sem seu consentimento. Na sp_helptext, você não consegue fazer isso:
Por padrão, a sp_showcode procurar os objetos apenas no banco atual (de novo, para evitar causar muito processamento). Mas, você pode procurar em outros bancos:
-- procura todas as procedures que contém a palavra venda em todos os bancos!
sp_showcode '%..%Venda%'
Pode ainda filtrar bancos específicos, por exemplo, suponha que você tenha bancos no formato LojaNNNN, onde NNNN é um código da loja:
sp_showcode 'Loja10%..%Venda%'
Se você quiser mandar exibir o texto de todas, use @all = 1
sp_showcode 'Loja10%..%Venda%' , @all = 1
E, se quiser exibir como um XML clicável no SSMS:
sp_showcode 'Loja10%..%Venda%' , 'xml', @all = '1'Descriptografia automática
Já experimentou tentar ver o código de uma procedure cruptografa com sp_helptext?
sp_helptext CryptProc
Mesmo se você tiver como DAC (conexão especial que um syadmin pode abrir e pode ter acesso a tabelas internas do sql), esse é o erro que vai receber. Com a sp_showcode, se você estiver conectado como DAC, ela vai descripotgrafar automaticamente a proc:
sp_showcode spencrypted
Se não estiver como DAC, ele vai gerar um aviso:
Vale lembrar que apenas sysadmins conseguem conectar como DAC. Isso é mais uma facilidade para aquele DBA que está investigando algo, ou precisa recuperar essas procs criada por alguém que não trabalha mais ali. Sem a sp_showcode ele iria conseguir fazer isso, com um pouco mais de trabalho… E, agora, com a sp_showcode, o maior trabalho que vai ter é abrir uma conexão como DAC.
DDL Triggers
Um outro tipo de objeto comum são triggers DDL que, originalemtne, a sp_helptext não consetue exibir:
Com a sp_showcode, é só fazer o que você aprendeu acima: passe o nome ou parte e ele vai trazer:
Fácil né? E lembrando que se você não lembra o nome exato, ou o mesmo o banco, é só usar o % para procurar.
Todas, exceto…
Uma grande funcionalidade (que sim, copei das procs de manuntenção do Ola, porque acho muito prático e incrível) é filtrar várias opções com exceção de outras.
Por exemplo, suponha que você queira encontrar todos os objetos com contém %test% no nome em todos os bancos, exceto esses que sejam do esquema sys. Com a sp_helptext, impossível… Mas com sp_showcode:
sp_showcode '%.%.%test%,-%.sys.%','xml',@all = 1
No caso eu usei a vírgula para especificar várias opções, e a segunda, é uma negação (começa com um -). Os demais parâmetros apenas fizeram exibir todos os resultados encontrados como XML. Para fazer isso sem a sp_showcode, você gastaria um bom tempo construindo suas queries e olhando em vários bancos.
Muitas opções
Eu criei a sp_showcode primariamente para ser usada como uma proc auxiliar, especialmente com o Management Studio. Mas, se você quiser cenários mais avançados você tem total controle sobre o resultado, com os demais parâmetros. Por exemplo, o modo export, retorna a definição original de cada procedure encontrada, e você pode usar para ler na sua aplicação, ou script powershell, por exemplo. Você consegue remover os headers incluídos, etc. Então, apesar do uso principal ser para facilitar o dia a dia no SSMS, ela ainda pode ser útil para, por exemplo, fazer um backup de várias procedures:
sqlcmd -S .\a25 -d Db1 -y 0 -Q "sp_showcode '%','exportgo',@all = 1, @type = 'proc,function,view,trigger' -- exporta todas os modulos de usuario encontrado no banco"Tem muitos outros parâmetros ali e deixei uma pequena documentação e exemplos de como usá-los. Convido você a explorá-la!
Eu ainda estou usando, testando e ajustando, então, ela pode sim conter erros e até alguns problemas de performance em certos casos mais extremos. Mas com o tempo, creio que irei deixá-la cada vez melhor e eficiente. Por isso, se for usar em produção, tome cuidado e observe o consumo. Quanto mais opções de busca você passar, mais ela terá trabalho, e, consequentemente, dependendo da quantidade de bancos e objetos, ela pode demorar.
Por anos, sempre que eu precisei fazer essas coisas, eu gastava muito tempo procurando pelo objeto, abrindo ou escrevendo a consulta nas tabelas internas. Pode parecer pouco, mas quando se trabalha com muito código todo o tempo, esses pequenos atalhos podem fazer você economizar muito tempo. E o objetivo dessa proc é flexibilizar a maneira como você tem acesso ao código dos objetos. Futuramente, penso em adicionar outros tipos, como Jobs e permitir buscar avançadas (quem sabe, até usando linguagem natural).
Lembre-se que a procedure é gratuita e disponível no meu repositório de Scrips SQL: sqlserver-lib/Modulos/sp.showcode.sql at main · rrg92/sqlserver-lib.
Se você tiver sugestões ou encontrou problemas, submenta issues ou as alterações e quando tiver um tempo reviso.
Muito obrigado e Até mais!
Head de Inovação – Power Tuning
Posts Relacionados:
Query Shortcuts
Entendendo o que é um banco de dados e o que faz um Administrador de Banco de dados (DBA)
CNPJ Alfanumérico: Como mudar no seu banco de dados SQL Server
WMI com SQL SERVER: Um exemplo prático que gerou até correção na documentação oficial do SQL
Uma imagem vale mais do que 10000000 linhas !