Aguarde… Carregando

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.

— 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:

— 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?

Cadastre seu e-mail para receber novos Posts e curta minha Página no Facebook para receber Dicas de Leituras 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.

Abraços,

Fabrício Lima

MCITP – Database Administrator

Consultor e Instrutor SQL Server

Trabalha com SQL Server desde 2006

16 thoughts on “Querys do Dia a Dia – Como encontrar um texto em Functions, Views e Procedures

  1. 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

  2. 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.

  3. 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

    1. 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

Deixe uma resposta

%d blogueiros gostam disto: