Querys do Dia a Dia – Como encontrar um texto em Functions, Views e Procedures
Fala Pessoal,
Antes de incluir/excluir/alterar um campo de uma tabela ou antes de incluir/excluir/alterar uma procedure, function ou view se faz necessário procurar no script do banco de dados se esses objetos que serão alterados são utilizados por alguma função, view ou SP.
Constantemente eu preciso realizar esse tipo de operação. Para isso, utilizo a query abaixo que procura um determinado texto em procedures, view e functions em uma determinada database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
-- Compatível com o SQL Server 2000 SELECT B.name , case type when 'P' then 'Stored procedure' when 'FN' then 'Function' when 'TF' then 'Function' when 'TR' then 'Trigger' when 'V' then 'View' else 'Outros Objetos' end FROM syscomments A (nolock) JOIN sysobjects B (nolock) on A.Id = B.Id WHERE A.Text like '%Nome_Objeto%' --Objto a ser procurado ORDER BY 2 DESC -- 2005/2008 SELECT type_desc, obj.name AS SP_NAME, sqlmod.definition AS SP_DEFINITION FROM sys.sql_modules AS sqlmod INNER JOIN sys.objects AS obj ON sqlmod.object_id = obj.object_id WHERE sqlmod.definition LIKE '%Nome_Objeto%' --Objto a ser procurado ORDER BY type_desc |
Entretanto, seu objeto ainda pode ser utilizado diretamente dentro de algum step de seus jobs. Para fazer uma busca em todos os Steps de Jobs por um texto, basta rodar a query abaixo:
|
1 2 3 4 5 6 |
-- Procura dentro de Job's SELECT name NOME_JOB, step_name ,command CODIGO, last_run_date FROM msdb.dbo.sysjobs A join msdb.dbo.sysjobsteps B on A.Job_id = B.Job_Id WHERE command like '%Nome_Objeto%' ORDER BY name |
Também existem ferramentas de terceiros que realizam essas consultas.
Nunca encontrei um modo parecido de realizar uma busca de um objeto, que está sendo utilizado dentro de uma task de um DTS(SQL Server 2000) ou SSIS (2005/2008). Caso exista, deixe um comentário que será de grande valor.
Gostou dessa Dica?
Curta, comente, compartilhe…
Assine meu canal no Youtube e curta minha página no Facebook para receber Dicas de Leituras, Vídeos e Eventos sobre SQL Server.
Aprenda a criar essa e outras rotinas para administrar melhor seu SQL Server no meu Treinamento de Tarefas do Dia a Dia de um DBA.
Até a próxima.
Fabrício Lima
MCITP – Database Administrator
Consultor e Instrutor SQL Server
Trabalha com SQL Server desde 2006
very útil, obrigada
Obrigado Andreia.
Resolveu meu problema.
Muito obrigado!
Que bom que ajudou Robert.
Abraços
Estou precisando consultar dentro de uma task de um DTS. Um ano após o post, será que já descobriu alguma forma? Rs
Desde já agradeço a atenção.
Abs
Infelizmente não Augusto…
Seria sensacional essa funcionalidade.
A única coisa que podemos fazer é usar procedures dentro das tasks.
Abraço.
Muito Obrigado!
Matou a pau brow! Muito útil
Obrigado Marcelo.
Fabricio, obrigado pelo post, muito util
De nada Ricardo.
Muito útil !!!
Excelente.
Obrigado Jorge.
Ola Senhores
eu precisava de um select para consulta dentro dos DTSX que foram subidos no servidor.
Exemplo buscar o nome de uma tabela dentro do XML do dtsx que subi para no servidor e me devolver o nome do DTSX.
Senhores,
Não sei se estou dizendo besteira, mas uma forma de pesquisar se um objeto é usado dentro de um pacote do SSIS/DTS é consultando o xml do pacote.
Quando o pacote era publicado no MSDB, ele podia ser consultado na tabela msdb.dbo.sysSSISPackages, lá você encontra os pacotes publicados, e uma coluna image chamada PackageData, basta converte-la para varbinary e depois para xml que você terá um conteúdo legível.
É possível também pegar um pacote do fileSystem e carrega-lo numa tabela do sql numa coluna varbinary e fazer as mesmas conversões para obter o mesmo resultado.
Um cuidado é simplesmente descobrir se o pacote é criptografado ou não, caso seja é necessário usar o dtUtil para retirar a criptografia primeiro.
Abaixo segue um link bem bacana (ou de uma “googlada” por querying ssis xml package): http://sqlblog.com/blogs/peter_debetta/archive/2006/07/13/Using-XML-Data-Type-Methods-to-query-SSIS-Packages.aspx
Lembrando que com a publicação no Catálogo a coisa muda, porque a tabela que contém os pacotes tem um valor em branco, não é permitido ler seu conteúdo porque ele foi criptografado com o CLR do bd usando a chave e um método de criptografia próprio.
Espero que tenha ajudado.
Abraços
Uma query inicial para ler o pacote do MSDB pode ser essa:
select
name PackName
, nullif(convert(varchar, convert(nvarchar, cast(Folderid AS varbinary(max)))) ,”) PackFolder
, convert(varchar, convert(nvarchar, CAST(Ownersid AS varbinary(max)))) PackOwner
, [description] As PackDescription
, createdate As PackCreateDate
, packagetype as PackType
, packageformat as PackFormat
, CAST(CAST(Packagedata AS VARBINARY(MAX)) AS XML) PackXML
from
–from msdb.dbo.sysdtspackages90 /*Extract to File System SQL 2005*/
–from msdb.dbo.sysssispackages /*Extract to File System SQL 2008*/
msdb.dbo.sysssispackages sp
O post é antigo, mas… não custa arriscar:
Alguma forma de verificar onde é utilizada uma Trigger?
É possível que o profile ou XEvent pegue isso, mas tem que dar uma pesquisada.
Nunca fiz.
Funcionou perfeitamente no SQL Server 2008
TOP.