{"id":2012,"date":"2025-08-17T19:43:45","date_gmt":"2025-08-17T22:43:45","guid":{"rendered":"https:\/\/thesqltimes.com\/blog\/?p=2012"},"modified":"2025-08-18T09:40:39","modified_gmt":"2025-08-18T12:40:39","slug":"sp_showcode-sp_helptext-evolucao","status":"publish","type":"post","link":"https:\/\/thesqltimes.com\/blog\/2025\/08\/17\/sp_showcode-sp_helptext-evolucao\/","title":{"rendered":"sp_showcode: A evolu\u00e7\u00e3o de sp_helptext que facilita sua busca por c\u00f3digo"},"content":{"rendered":"<div class=\"pld-like-dislike-wrap pld-template-1\">\r\n    <div class=\"pld-like-wrap  pld-common-wrap\">\r\n    <a href=\"javascript:void(0)\" class=\"pld-like-trigger pld-like-dislike-trigger  \" title=\"Muito \u00fatil!\" data-post-id=\"2012\" data-trigger-type=\"like\" data-restriction=\"cookie\" data-already-liked=\"0\">\r\n                        <i class=\"fas fa-thumbs-up\"><\/i>\r\n                <\/a>\r\n    <span class=\"pld-like-count-wrap pld-count-wrap\">2    <\/span>\r\n<\/div><\/div><span class=\"span-reading-time rt-reading-time\" style=\"display: block;\"><span class=\"rt-label rt-prefix\">Tempo de Leitura:<\/span> <span class=\"rt-time\"> 8<\/span> <span class=\"rt-label rt-postfix\">minutos<\/span><\/span>\n<p>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\u00e7\u00f5es e\/ou triggers&#8230; objetos que tem um c\u00f3digo que fica salvo ali no banco. E uma a\u00e7\u00e3o muito comum para quem mexe com isso \u00e9 lidar frequentemente com o c\u00f3digo, seja para alterar ou apenas ler para entender ou confirmar algum comportamento. H\u00e1 quem prefira usar o SSMS, navegar na estrutura hier\u00e1rquica, clicar com o bot\u00e3o direito e mandar abrir. E, assim como esse que vos fala, h\u00e1 esses que preferem ali direto da janela de c\u00f3digo mandar exibir o texto com a famosa <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/system-stored-procedures\/sp-helptext-transact-sql?view=sql-server-ver17\">sp_helptext<\/a> (que h\u00e1 12 anos foi um dos <a href=\"https:\/\/thesqltimes.com\/blog\/2013\/08\/29\/relmpago-como-usar-sp_helptext\/\" data-type=\"post\" data-id=\"234\">primeiros posts nesse blog<\/a>), que eu acho muito mais r\u00e1pido do que usar a interface.<\/p>\n\n\n\n<figure class=\"wp-block-video\"><video controls loop preload=\"auto\" src=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/SSMS_AT8vq1cNu6.mp4\"><\/video><figcaption class=\"wp-element-caption\">Como ver o c\u00f3digo de uma procedure pelo SSMS<\/figcaption><\/figure>\n\n\n\n<figure class=\"wp-block-video\"><video controls src=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/SSMS_g5ArC1Hrwp.mp4\"><\/video><figcaption class=\"wp-element-caption\">sp_helptext vs sp_showcode: note que a sp_helptext retorna uma tabela com v\u00e1rias linhas, enquanto a sp_showcode faz o print direto pro output, independente do tipo escolhido no SSMS.<\/figcaption><\/figure>\n\n\n\n<p>A <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/system-stored-procedures\/sp-helptext-transact-sql?view=sql-server-ver17\">sp_helptext<\/a> originalmente, retorna o c\u00f3digo como 1 coluna em v\u00e1rias linhas, onde cada linha \u00e9 uma linha do c\u00f3digo fonte. Se voc\u00ea copia diretamente e joga no SSMS, voc\u00ea perde formata\u00e7\u00e3o. Para contornar isso, voc\u00ea pode ativar o modo texto no SSMS (atalho CTRL + T) e <em>voi l\u00e1<\/em>: Sua proc, por mais complexa que seja , retorna com a formata\u00e7\u00e3o correta e muito mais f\u00e1cil de copiar&#8230; Aparentemente&#8230; Pois, em alguns casos, as linhas podem estar quebradas no lugar errado e o cabe\u00e7alho da coluna <em>Text<\/em>, vem junto&#8230; Quase bom, mas em 99% dos casos, ela segue sendo bem \u00fatil e acho melhor do que ir procurar na interface. Mas, as limita\u00e7\u00f5es n\u00e3o param por a\u00ed: <\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>eu preciso saber exatamente o que estou buscando. Se eu sei apenas parte do nome do objeto, preciso ir l\u00e1 na sys.objects ou sys.procedures, achar o nome exato e ent\u00e3o chamar a sp_helptext, ou usar uma fun\u00e7\u00e3o, tipo a <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/functions\/object-definition-transact-sql?view=sql-server-ver17\">OBJECT_DEFINITION<\/a>. <\/li>\n\n\n\n<li>Outra dor \u00e9 que ela n\u00e3o descriptografa sozinho, mesmo se for sysadmin, mesmo se eu tiver conectado como DAC<\/li>\n\n\n\n<li>E, se eu quiser Triggers DDL, trigger de logon? esquece, a sp_helptext n\u00e3o acha&#8230;<\/li>\n<\/ul>\n\n\n\n<p>\u00c9 aqui que eu vos apresento uma evolu\u00e7\u00e3o depois de tanto usar a sp_helptext na minha vida: <a href=\"https:\/\/github.com\/rrg92\/sqlserver-lib\/blob\/main\/Modulos\/sp.showcode.sql\">sp_showcode<\/a><\/p>\n\n\n\n<p>Como j\u00e1 falei demais at\u00e9 aqui, vamos h\u00e1 algumas compara\u00e7\u00f5es para voc\u00ea entender que ganhos essa nova proc que criei pode trazer no seu dia a dia.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Sa\u00edda e Formata\u00e7\u00e3o<\/h2>\n\n\n\n<p>Com a sp_helptext, se eu mandar exibir o c\u00f3digo da procedure MinhaProc, ele vai exibir assim por padr\u00e3o:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#24292e;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" style=\"color:#24292e;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>sp_helptext MinhaProc<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki github-light\" style=\"background-color: #fff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #24292E\">sp_helptext MinhaProc<\/span><\/span><\/code><\/pre><\/div>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><a href=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image.png\"><img loading=\"lazy\" decoding=\"async\" width=\"342\" height=\"365\" src=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image.png\" alt=\"\" class=\"wp-image-2022\" srcset=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image.png 342w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-281x300.png 281w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-16x16.png 16w\" sizes=\"auto, (max-width: 342px) 100vw, 342px\" \/><\/a><\/figure><\/div>\n\n\n<p>Note que \u00e9 uma tabela com 1 coluna chamada Text, e o resultado. Se eu quiser exibir como texto, eu aperto a op\u00e7\u00e3o no SSMS (ou uso o atalho CTRL + T) e:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-3.png\"><img loading=\"lazy\" decoding=\"async\" width=\"801\" height=\"435\" src=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-3.png\" alt=\"\" class=\"wp-image-2025\" srcset=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-3.png 801w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-3-300x163.png 300w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-3-768x417.png 768w\" sizes=\"auto, (max-width: 801px) 100vw, 801px\" \/><\/a><figcaption class=\"wp-element-caption\">apesar da formata\u00e7\u00e3o parecer ok, ainda tem o cabe\u00e7alho com o texto &#8220;text&#8221;, que se copiar, vai junto e voc\u00ea tem que remover&#8230; Parece pouca coisa, mas quem faz isso muito, se torna algo repetitivo e chato<\/figcaption><\/figure>\n\n\n\n<p>Assim, a fomata\u00e7\u00e3o \u00e9 mantida, mas nem tudo s\u00e3o flores. Esse \u00e9 um caso mais bizarro, usando a stpPowerAlert_Log_Full, uma procedure padr\u00e3o do <a href=\"https:\/\/poweralerts.com.br\/\">Power Alerts<\/a> que alerta sobre o uso de logs:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-1.png\"><img loading=\"lazy\" decoding=\"async\" width=\"673\" height=\"368\" src=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-1.png\" alt=\"\" class=\"wp-image-2023\" srcset=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-1.png 673w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-1-300x164.png 300w\" sizes=\"auto, (max-width: 673px) 100vw, 673px\" \/><\/a><figcaption class=\"wp-element-caption\">A sp_helptext acabou quebrando uma linha onde n\u00e3o deveria.<\/figcaption><\/figure>\n\n\n\n<p>A sp_showcode traz v\u00e1rias melhorias aqui. A primeira \u00e9 que voc\u00ea n\u00e3o precisa ativar o modo texto do SSMS:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-4.png\"><img loading=\"lazy\" decoding=\"async\" width=\"784\" height=\"477\" src=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-4.png\" alt=\"\" class=\"wp-image-2026\" srcset=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-4.png 784w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-4-300x183.png 300w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-4-768x467.png 768w\" sizes=\"auto, (max-width: 784px) 100vw, 784px\" \/><\/a><figcaption class=\"wp-element-caption\">Note que, mesmo com a op\u00e7\u00e3o de Grid default ativada, a sp_showcode mandou jogar ali pra sa\u00edda em texto<\/figcaption><\/figure>\n\n\n\n<p>E, ela mant\u00e9m a formata\u00e7\u00e3o id\u00eantica:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-5.png\"><img loading=\"lazy\" decoding=\"async\" width=\"924\" height=\"420\" src=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-5.png\" alt=\"\" class=\"wp-image-2027\" srcset=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-5.png 924w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-5-300x136.png 300w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-5-768x349.png 768w\" sizes=\"auto, (max-width: 924px) 100vw, 924px\" \/><\/a><figcaption class=\"wp-element-caption\">A linha ficou exatamente igual como foi criada<\/figcaption><\/figure>\n\n\n\n<p>Note que ele j\u00e1 joga tudo ali f\u00e1cil para voc\u00ea copiar. Apesar da grande maioria dos casos voc\u00ea n\u00e3o ter problemas com essa sa\u00edda, ela tem apenas uma desvantagem: o tamanho m\u00e1ximo da linha \u00e9 de 4000 caracteres (a sp_helptext tenta justamente contornar esse problema, mas piora a situa\u00e7\u00e3o rs). Se voc\u00ea tiver um objeto com c\u00f3digo que tenha alguma linha com mais de 4000 caracteres, <s>procure um m\u00e9dico,<\/s> ent\u00e3o, a sp_showcode vai jogar pra linha seguinte, e a\u00ed ficar diferente do original. Mas, ao longo de 10 anos como DBA, eu acho que nunca vi c\u00f3digo com uma linha t\u00e3o grande assim, nem mesmos as procs internas criadas pela Microsoft&#8230; e c\u00e1 entre n\u00f3s, se algu\u00e9m entupiu mais de 4000 caracteres em uma \u00fanica linha, \u00e9 porque n\u00e3o quer que voc\u00ea leia f\u00e1cil essa proc rs!<\/p>\n\n\n\n<p>Mas, se ainda sim voc\u00ea n\u00e3o quiser a sa\u00edda em texto para estes casos, pode trocar pra XML:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#24292e;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" style=\"color:#24292e;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>sp_showcode MinhaProc,'xml'<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki github-light\" style=\"background-color: #fff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #24292E\">sp_showcode MinhaProc,<\/span><span style=\"color: #032F62\">&#39;xml&#39;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-6.png\"><img loading=\"lazy\" decoding=\"async\" width=\"737\" height=\"298\" src=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-6.png\" alt=\"\" class=\"wp-image-2029\" srcset=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-6.png 737w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-6-300x121.png 300w\" sizes=\"auto, (max-width: 737px) 100vw, 737px\" \/><\/a><\/figure>\n\n\n\n<p>Clicando na coluna ObjectDefinition retornada, uma nova aba \u00e9 aberta:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-7.png\"><img loading=\"lazy\" decoding=\"async\" width=\"898\" height=\"229\" src=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-7.png\" alt=\"\" class=\"wp-image-2030\" srcset=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-7.png 898w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-7-300x77.png 300w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-7-768x196.png 768w\" sizes=\"auto, (max-width: 898px) 100vw, 898px\" \/><\/a><figcaption class=\"wp-element-caption\">basta copiar e colar para uma aba de SQL. Esse \u00e9 o mesmo comportamento da sp_whoisactive<\/figcaption><\/figure>\n\n\n\n<p>Nesse modo voc\u00ea s\u00f3 vai ter problemas se usar algum caracter inv\u00e1lido XML. A proc tenta subsituir os inv\u00e1lidos mais prov\u00e1veis por ?, mas pode ter algum que n\u00e3o mapeamos. Mas, geralmente, esse caracteres s\u00e3o de controle, invis\u00edveis, ent\u00e3o, dificilmente voc\u00ea usar\u00e1 isso na proc. Caso encontre esse problema, e, se for poss\u00edvel, reporte o caracter inv\u00e1lido como uma issue, para pensarmos em melhorias. De novo: esse \u00e9 um problema que s\u00f3 vai acontecer com a sa\u00edda XML.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Nome do objeto: Procure por uma parte<\/h2>\n\n\n\n<p>\u00c9 bem comum voc\u00ea mexer com v\u00e1rias procedures em algum projeto como em prcListarVendas, prcCadastraVenda, etc. As vezes voc\u00ea n\u00e3o 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\u00ea pode usar o % para fazer um LIKE:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#24292e;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" style=\"color:#24292e;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>sp_showcode '%Venda%'<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki github-light\" style=\"background-color: #fff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #24292E\">sp_showcode <\/span><span style=\"color: #032F62\">&#39;%Venda%&#39;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-8.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"493\" src=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-8-1024x493.png\" alt=\"\" class=\"wp-image-2043\" srcset=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-8-1024x493.png 1024w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-8-300x145.png 300w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-8-768x370.png 768w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-8.png 1314w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/a><figcaption class=\"wp-element-caption\">Antes da sp_showcode, eu iria fazer um select * from sys.objects where name like &#8216;%venda%&#8217; e analisar o resultado, para s\u00f3 ent\u00e3o, usar a sp_helptext. Com a sp_showcode, \u00e9 tudo ali!<\/figcaption><\/figure>\n\n\n\n<p>Quando voc\u00ea especifica o % e a sp_showcode encontra mais de um op\u00e7\u00e3o, ela entende que voc\u00ea n\u00e3o sabe o que quer, e apenas te mostra as op\u00e7\u00f5es. Voc\u00ea pode copiar o nome certo ou mudar alguns par\u00e2metros que alterem esse comportamento. Isso \u00e9 para evitar ler o c\u00f3digo de v\u00e1rios objetos, que pode ser muita cosia e causar algum impacto no servidor sem seu consentimento. Na sp_helptext, voc\u00ea n\u00e3o consegue fazer isso:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-9.png\"><img loading=\"lazy\" decoding=\"async\" width=\"735\" height=\"332\" src=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-9.png\" alt=\"\" class=\"wp-image-2044\" srcset=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-9.png 735w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-9-300x136.png 300w\" sizes=\"auto, (max-width: 735px) 100vw, 735px\" \/><\/a><\/figure>\n\n\n\n<p>Por padr\u00e3o, a sp_showcode procurar os objetos apenas no banco atual (de novo, para evitar causar muito processamento). Mas, voc\u00ea pode procurar em outros bancos:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#24292e;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" style=\"color:#24292e;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly> -- procura todas as procedures que cont\u00e9m a palavra venda em todos os bancos!\nsp_showcode '%..%Venda%'<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki github-light\" style=\"background-color: #fff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #24292E\"> <\/span><span style=\"color: #6A737D\">-- procura todas as procedures que cont\u00e9m a palavra venda em todos os bancos!<\/span><\/span>\n<span class=\"line\"><span style=\"color: #24292E\">sp_showcode <\/span><span style=\"color: #032F62\">&#39;%..%Venda%&#39;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-11.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"593\" src=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-11-1024x593.png\" alt=\"\" class=\"wp-image-2046\" srcset=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-11-1024x593.png 1024w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-11-300x174.png 300w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-11-768x445.png 768w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-11.png 1333w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/a><figcaption class=\"wp-element-caption\">Colocando o %..Filtro, voc\u00ea especifica que quer olhar em outros bancos al\u00e9m do atual<\/figcaption><\/figure>\n\n\n\n<p>Pode ainda filtrar bancos espec\u00edficos, por exemplo, suponha que voc\u00ea tenha bancos no formato LojaNNNN, onde NNNN \u00e9 um c\u00f3digo da loja:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#24292e;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" style=\"color:#24292e;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>sp_showcode 'Loja10%..%Venda%'<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki github-light\" style=\"background-color: #fff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #24292E\">sp_showcode <\/span><span style=\"color: #032F62\">&#39;Loja10%..%Venda%&#39;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-12.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"569\" src=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-12-1024x569.png\" alt=\"\" class=\"wp-image-2047\" srcset=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-12-1024x569.png 1024w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-12-300x167.png 300w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-12-768x427.png 768w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-12.png 1253w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/a><figcaption class=\"wp-element-caption\">Filtrando as procedures dos bancos da Loja10NN<\/figcaption><\/figure>\n\n\n\n<p>Se voc\u00ea quiser mandar exibir o texto de todas, use @all = 1<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#24292e;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" style=\"color:#24292e;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>sp_showcode 'Loja10%..%Venda%' , @all = 1<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki github-light\" style=\"background-color: #fff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #24292E\">sp_showcode <\/span><span style=\"color: #032F62\">&#39;Loja10%..%Venda%&#39;<\/span><span style=\"color: #24292E\"> , @all <\/span><span style=\"color: #D73A49\">=<\/span><span style=\"color: #24292E\"> <\/span><span style=\"color: #005CC5\">1<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-14.png\"><img loading=\"lazy\" decoding=\"async\" width=\"795\" height=\"616\" src=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-14.png\" alt=\"\" class=\"wp-image-2049\" srcset=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-14.png 795w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-14-300x232.png 300w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-14-768x595.png 768w\" sizes=\"auto, (max-width: 795px) 100vw, 795px\" \/><\/a><figcaption class=\"wp-element-caption\">o par\u00e2metro @all = 1 manda exibir o texto de tudo que encontrar pela frente. Use com cuidado<\/figcaption><\/figure>\n\n\n\n<p>E, se quiser exibir como um XML clic\u00e1vel no SSMS:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#24292e;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" style=\"color:#24292e;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>sp_showcode 'Loja10%..%Venda%' , 'xml', @all = '1'<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki github-light\" style=\"background-color: #fff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #24292E\">sp_showcode <\/span><span style=\"color: #032F62\">&#39;Loja10%..%Venda%&#39;<\/span><span style=\"color: #24292E\"> , <\/span><span style=\"color: #032F62\">&#39;xml&#39;<\/span><span style=\"color: #24292E\">, @all <\/span><span style=\"color: #D73A49\">=<\/span><span style=\"color: #24292E\"> <\/span><span style=\"color: #032F62\">&#39;1&#39;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<figure class=\"wp-block-video\"><video controls src=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/SSMS_fJB7gNYJPJ.mp4\"><\/video><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Descriptografia autom\u00e1tica<\/h2>\n\n\n\n<p>J\u00e1 experimentou tentar ver o c\u00f3digo de uma procedure cruptografa com sp_helptext?<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#24292e;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" style=\"color:#24292e;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>sp_helptext CryptProc<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki github-light\" style=\"background-color: #fff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #24292E\">sp_helptext CryptProc<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-15.png\"><img loading=\"lazy\" decoding=\"async\" width=\"561\" height=\"319\" src=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-15.png\" alt=\"\" class=\"wp-image-2051\" srcset=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-15.png 561w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-15-300x171.png 300w\" sizes=\"auto, (max-width: 561px) 100vw, 561px\" \/><\/a><\/figure>\n\n\n\n<p>Mesmo se voc\u00ea tiver como DAC (conex\u00e3o especial que um syadmin pode abrir e pode ter acesso a tabelas internas do sql), esse \u00e9 o erro que vai receber. Com a sp_showcode, se voc\u00ea estiver conectado como DAC, ela vai descripotgrafar automaticamente a proc:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#24292e;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" style=\"color:#24292e;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>sp_showcode spencrypted<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki github-light\" style=\"background-color: #fff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #24292E\">sp_showcode spencrypted<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-16.png\"><img loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"467\" src=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-16.png\" alt=\"\" class=\"wp-image-2052\" srcset=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-16.png 624w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-16-300x225.png 300w\" sizes=\"auto, (max-width: 624px) 100vw, 624px\" \/><\/a><\/figure>\n\n\n\n<p>Se n\u00e3o estiver como DAC, ele vai gerar um aviso:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-17.png\"><img loading=\"lazy\" decoding=\"async\" width=\"665\" height=\"317\" src=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-17.png\" alt=\"\" class=\"wp-image-2053\" srcset=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-17.png 665w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-17-300x143.png 300w\" sizes=\"auto, (max-width: 665px) 100vw, 665px\" \/><\/a><\/figure>\n\n\n\n<p>Vale lembrar que apenas sysadmins conseguem conectar como DAC. Isso \u00e9 mais uma facilidade para aquele DBA que est\u00e1 investigando algo, ou precisa recuperar essas procs criada por algu\u00e9m que n\u00e3o trabalha mais ali. Sem a sp_showcode ele iria conseguir fazer isso, com um pouco mais de trabalho&#8230; E, agora, com a sp_showcode, o maior trabalho que vai ter \u00e9 abrir uma conex\u00e3o como DAC.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">DDL Triggers<\/h2>\n\n\n\n<p>Um outro tipo de objeto comum s\u00e3o triggers DDL que, originalemtne, a sp_helptext n\u00e3o consetue exibir:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-18.png\"><img loading=\"lazy\" decoding=\"async\" width=\"860\" height=\"335\" src=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-18.png\" alt=\"\" class=\"wp-image-2056\" srcset=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-18.png 860w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-18-300x117.png 300w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-18-768x299.png 768w\" sizes=\"auto, (max-width: 860px) 100vw, 860px\" \/><\/a><\/figure>\n\n\n\n<p>Com a sp_showcode, \u00e9 s\u00f3 fazer o que voc\u00ea aprendeu acima: passe o nome ou parte e ele vai trazer:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-19.png\"><img loading=\"lazy\" decoding=\"async\" width=\"788\" height=\"458\" src=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-19.png\" alt=\"\" class=\"wp-image-2057\" srcset=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-19.png 788w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-19-300x174.png 300w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-19-768x446.png 768w\" sizes=\"auto, (max-width: 788px) 100vw, 788px\" \/><\/a><\/figure>\n\n\n\n<p>F\u00e1cil n\u00e9? E lembrando que se voc\u00ea n\u00e3o lembra o nome exato, ou o mesmo o banco, \u00e9 s\u00f3 usar o % para procurar.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Todas, exceto&#8230;<\/h2>\n\n\n\n<p>Uma grande funcionalidade (que sim, copei das <a href=\"https:\/\/ola.hallengren.com\/\">procs de manunten\u00e7\u00e3o do Ola<\/a>, porque acho muito pr\u00e1tico e incr\u00edvel) \u00e9 filtrar v\u00e1rias op\u00e7\u00f5es com exce\u00e7\u00e3o de outras.<\/p>\n\n\n\n<p>Por exemplo, suponha que voc\u00ea queira encontrar todos os objetos com cont\u00e9m %test% no nome em todos os bancos, exceto esses que sejam do esquema sys. Com a sp_helptext, imposs\u00edvel&#8230; Mas com sp_showcode:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#24292e;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" style=\"color:#24292e;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>sp_showcode '%.%.%test%,-%.sys.%','xml',@all = 1<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki github-light\" style=\"background-color: #fff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #24292E\">sp_showcode <\/span><span style=\"color: #032F62\">&#39;%.%.%test%,-%.sys.%&#39;<\/span><span style=\"color: #24292E\">,<\/span><span style=\"color: #032F62\">&#39;xml&#39;<\/span><span style=\"color: #24292E\">,@all <\/span><span style=\"color: #D73A49\">=<\/span><span style=\"color: #24292E\"> <\/span><span style=\"color: #005CC5\">1<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-20.png\"><img loading=\"lazy\" decoding=\"async\" width=\"885\" height=\"325\" src=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-20.png\" alt=\"\" class=\"wp-image-2058\" srcset=\"https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-20.png 885w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-20-300x110.png 300w, https:\/\/thesqltimes.com\/blog\/wp-content\/uploads\/2025\/08\/image-20-768x282.png 768w\" sizes=\"auto, (max-width: 885px) 100vw, 885px\" \/><\/a><\/figure>\n\n\n\n<p>No caso eu usei a v\u00edrgula para especificar v\u00e1rias op\u00e7\u00f5es, e a segunda, \u00e9 uma nega\u00e7\u00e3o (come\u00e7a com um -). Os demais par\u00e2metros apenas fizeram exibir todos os resultados encontrados como XML. Para fazer isso sem a sp_showcode, voc\u00ea gastaria um bom tempo construindo suas queries e olhando em v\u00e1rios bancos.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Muitas op\u00e7\u00f5es<\/h2>\n\n\n\n<p>Eu criei a sp_showcode primariamente para ser usada como uma proc auxiliar, especialmente com o Management Studio. Mas, se voc\u00ea quiser cen\u00e1rios mais avan\u00e7ados voc\u00ea tem total controle sobre o resultado, com os demais par\u00e2metros. Por exemplo, o modo export, retorna a defini\u00e7\u00e3o original de cada procedure encontrada, e voc\u00ea pode usar para ler na sua aplica\u00e7\u00e3o, ou script powershell, por exemplo. Voc\u00ea consegue remover os headers inclu\u00eddos, etc. Ent\u00e3o, apesar do uso principal ser para facilitar o dia a dia no SSMS, ela ainda pode ser \u00fatil para, por exemplo, fazer um backup de v\u00e1rias procedures:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#24292e;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" style=\"color:#24292e;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>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\"<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki github-light\" style=\"background-color: #fff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #24292E\">sqlcmd <\/span><span style=\"color: #D73A49\">-<\/span><span style=\"color: #24292E\">S .\\a25 <\/span><span style=\"color: #D73A49\">-<\/span><span style=\"color: #24292E\">d Db1 <\/span><span style=\"color: #D73A49\">-<\/span><span style=\"color: #24292E\">y <\/span><span style=\"color: #005CC5\">0<\/span><span style=\"color: #24292E\"> <\/span><span style=\"color: #D73A49\">-<\/span><span style=\"color: #24292E\">Q <\/span><span style=\"color: #032F62\">&quot;sp_showcode &#39;%&#39;,&#39;exportgo&#39;,@all = 1, @type = &#39;proc,function,view,trigger&#39; -- exporta todas os modulos de usuario encontrado no banco&quot;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p>Tem muitos outros par\u00e2metros ali e deixei uma pequena documenta\u00e7\u00e3o e exemplos de como us\u00e1-los. Convido voc\u00ea a explor\u00e1-la! <\/p>\n\n\n\n<p>Eu ainda estou usando, testando e ajustando, ent\u00e3o, ela pode sim conter erros e at\u00e9 alguns problemas de performance em certos casos mais extremos. Mas com o tempo, creio que irei deix\u00e1-la cada vez melhor  e eficiente. Por isso, se for usar em produ\u00e7\u00e3o, tome cuidado e observe o consumo. Quanto mais op\u00e7\u00f5es de busca voc\u00ea passar, mais ela ter\u00e1 trabalho, e, consequentemente, dependendo da quantidade de bancos e objetos, ela pode demorar.<\/p>\n\n\n\n<p>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\u00f3digo todo o tempo, esses pequenos atalhos podem fazer voc\u00ea economizar muito tempo. E o objetivo dessa proc \u00e9 flexibilizar a maneira como voc\u00ea tem acesso ao c\u00f3digo dos objetos. Futuramente, penso em adicionar outros tipos, como Jobs e permitir buscar avan\u00e7adas (quem sabe, at\u00e9 usando linguagem natural).<\/p>\n\n\n\n<p>Lembre-se que a procedure \u00e9 gratuita e dispon\u00edvel no meu reposit\u00f3rio de Scrips SQL: <a href=\"https:\/\/github.com\/rrg92\/sqlserver-lib\/blob\/main\/Modulos\/sp.showcode.sql\">sqlserver-lib\/Modulos\/sp.showcode.sql at main \u00b7 rrg92\/sqlserver-lib<\/a>. <\/p>\n\n\n\n<p>Se voc\u00ea tiver sugest\u00f5es ou encontrou problemas, submenta issues ou as altera\u00e7\u00f5es e quando tiver um tempo reviso.<\/p>\n\n\n\n<p>Muito obrigado e At\u00e9 mais!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>2 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\u00e7\u00f5es e\/ou triggers&#8230; objetos que tem um c\u00f3digo que fica salvo ali&hellip;&nbsp;<a href=\"https:\/\/thesqltimes.com\/blog\/2025\/08\/17\/sp_showcode-sp_helptext-evolucao\/\" rel=\"bookmark\"><span class=\"screen-reader-text\">sp_showcode: A evolu\u00e7\u00e3o de sp_helptext que facilita sua busca por c\u00f3digo<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":2058,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"[\"title\",\"meta\",\"content\",\"tags\",\"comments\"]","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","footnotes":""},"categories":[8,11],"tags":[465,467,468,466,464,35,462,428,426,460,463,73,80,461,87],"series":[],"class_list":["post-2012","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administracao","category-desenvolvimento","tag-body","tag-codigo","tag-codigo-2","tag-codigo-fonte","tag-corpo","tag-ddl","tag-definition","tag-function","tag-procedure","tag-source","tag-source-code","tag-sql-server","tag-text","tag-trigger","tag-windows"],"_links":{"self":[{"href":"https:\/\/thesqltimes.com\/blog\/wp-json\/wp\/v2\/posts\/2012","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/thesqltimes.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/thesqltimes.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/thesqltimes.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/thesqltimes.com\/blog\/wp-json\/wp\/v2\/comments?post=2012"}],"version-history":[{"count":33,"href":"https:\/\/thesqltimes.com\/blog\/wp-json\/wp\/v2\/posts\/2012\/revisions"}],"predecessor-version":[{"id":2075,"href":"https:\/\/thesqltimes.com\/blog\/wp-json\/wp\/v2\/posts\/2012\/revisions\/2075"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/thesqltimes.com\/blog\/wp-json\/wp\/v2\/media\/2058"}],"wp:attachment":[{"href":"https:\/\/thesqltimes.com\/blog\/wp-json\/wp\/v2\/media?parent=2012"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/thesqltimes.com\/blog\/wp-json\/wp\/v2\/categories?post=2012"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/thesqltimes.com\/blog\/wp-json\/wp\/v2\/tags?post=2012"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/thesqltimes.com\/blog\/wp-json\/wp\/v2\/series?post=2012"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}