none
Select que verifica a utilização de uma view ou campo. RRS feed

  • Pergunta

  • Pessoal.

    Tem como fazer um select que busque nas views, procedures e triggers de um banco, e saber por exemplo se uma VIEW, TABELA, ou campo estão sendo usados.

    Por exemplo em uma view o seu código é assim:

    CREATE VIEW teste
    Select campo1 from Tabela1

    Em uma procedure o código é assim:

    CREATE PROCEDURE teste
    AS
    BEGIN

    Select campo1 from tabela1

    END


    Então queria um select que mostrasse por exemplo

    se procurar pela "tabela1" mostraria assim:
     
    Tipo            | Nome | Comando
    View           | teste   |  select campo1 from tabela1
    Procedure    | teste   | create procedure teste as begin select campo1 from tabela1 end


    EStou precisando disto, porque estou matando um sistema e refazendo tudo em outro, então estou descontinuando o sistema aos poucos. só que além de descontinuar o sistema estou descontinuando as views, procedures e outros affim..

    Só que não posso simplesmente descontinuar uma view ou procedure, sem saber se ela é usada em outra view, trigger, procedure....

    É muito complexo o que estou tentando fazer?
    quarta-feira, 16 de setembro de 2009 13:28

Respostas

  • Segue um sql para ti executar. Altera o nome do objeto no where e vai listar os comandos onde o o objeto está referenciado. (SQL2005)
    SELECT
    sp.type TIPO,
    sp.name NOME,
    ISNULL(smsp.definition, ssmsp.definition) COMANDO
    FROM
    sys.all_objects AS sp
    LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
    LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
    WHERE
    smsp.definition like '%NOMEOBJETO%' OR  ssmsp.definition like '%NOMEOBJETO%'

    Se resolver para ti cria uma SP .

    • Marcado como Resposta New Civic quinta-feira, 17 de setembro de 2009 19:56
    quinta-feira, 17 de setembro de 2009 13:58

Todas as Respostas

  • New Civic,

    o campo text da syscomments tem os códigos dos objetos view, proc, etc.

    Caso esteja criptografado, o select simples nessa tabela não irá resolver e no SQL Server 2000 pelo menos você pode usar ferramentas de terceiro para "descriptografar" procs.

    Em relação ao uso, acho que depende da versão do SQL Server. Na 2000 não existem as view dm*, enquanto no 2005 e 2008 pode ter algo para monitorar qual objeto está sendo usado, mas não tenho certeza.

    Espero ter ajudado.

    Alex
    quarta-feira, 16 de setembro de 2009 13:34
  • New Civic,

           Se você estiver utilizando SQL 2000, você pode usar a view sys.sysdepends. 
          
           Se estiver utilizando SQL 2005 ou 2008, use:
           sys.sql_expression_dependencies 
           sys.dm_sql_referenced_entities
           sys.dm_sql_referencing_entities

        Exemplos:

        Retornando todos os objetos que são referenciados por outros objetos

    USE AdventureWorks;
    GO
    SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name, 
        o.type_desc AS referencing_desciption, 
        COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id, 
        referencing_class_desc, referenced_class_desc,
        referenced_server_name, referenced_database_name, referenced_schema_name,
        referenced_entity_name, 
        COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,
        is_caller_dependent, is_ambiguous
    FROM sys.sql_expression_dependencies AS sed
    INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
    WHERE referencing_id = OBJECT_ID(N'Production.vProductAndDescription');
    GO



        Retornando todos os objetos que referenciam à um objeto

    USE AdventureWorks;
    GO
    SELECT OBJECT_SCHEMA_NAME ( referencing_id ) AS referencing_schema_name,
        OBJECT_NAME(referencing_id) AS referencing_entity_name, 
        o.type_desc AS referencing_desciption, 
        COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id, 
        referencing_class_desc, referenced_class_desc,
        referenced_server_name, referenced_database_name, referenced_schema_name,
        referenced_entity_name, 
        COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,
        is_caller_dependent, is_ambiguous
    FROM sys.sql_expression_dependencies AS sed
    INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
    WHERE referenced_id = OBJECT_ID(N'Production.Product');
    GO

    MCT / MCITP - Database Administrator 2008 MCITP - Database Developer 2008
    quarta-feira, 16 de setembro de 2009 14:16
    Moderador
  • Caros,

    testei as duas soluções e não fui muito feliz.

    Eu teria que achar uma forma de pesquisar no código fonte de triggers, views e procedures..

    eu imaginei algo assim

    SELECT
    ...
    FROM
    ....
    WHERE CAMPO LIKE '%TABELA1%'

    porém não sei como fazer..

    uma vez um consultor digitou uma procedure que mostrava o comando inteiro que estava rodando.. acho que era sp_help.. algo parecido..

    att
    new civic
    quarta-feira, 16 de setembro de 2009 16:33
  • Civic,

    Acredito que desta forma vai ficar praticamente inviável.

    Pesquisar dentro do código!!!!
    Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA
    quarta-feira, 16 de setembro de 2009 18:31
  • mas uma vez eu vi um consultor do nosso erp, digitar um comando dentro sql que mostrou uma view que eu estava executando.. ele capturou o comando e rodou manualmente..

    não lembro se era sp_help ou algo assim..

    EU penso assim. uma procedure, uma trigger, uma view.. não é um meta dado?

    Não está gravado em algum lugar ? Se não estivesse gravado em algum lugar como poderiamos fazer um edit view, edit procedure???

    bem.. tudo bem.. vou pesquisar uma forma.. é que para mim ficará muito dificil tirar parte do sistema e contar com a sorte para ver se está sendo usado (em outro lugar) algo que tirei..

    Abraço
    New Civic

    quarta-feira, 16 de setembro de 2009 20:41
  • Qual é a versão do SQL Server usado?
    Talvez o query analyzer não seja a melhor ferramenta para fazer esse levantamento. Você tentou rodar trace/profiles para fazer este levantamento?

    Agora eu fiquei curioso. Caso você consiga algo, coloque um post aqui para sabermos qual foi a solução.

    Alex

    quinta-feira, 17 de setembro de 2009 13:13
  • Olá,

          Sim, você pode usar a sp_help. Veja o exemplo (Ajuste o nome para o nome da sua view):

         
    USE <Seu_banco>;
    GO
    EXEC sp_help 'Nome_da_View';
    GO

    MCT / MCITP - Database Administrator 2008 MCITP - Database Developer 2008
    quinta-feira, 17 de setembro de 2009 13:39
    Moderador
  • Segue um sql para ti executar. Altera o nome do objeto no where e vai listar os comandos onde o o objeto está referenciado. (SQL2005)
    SELECT
    sp.type TIPO,
    sp.name NOME,
    ISNULL(smsp.definition, ssmsp.definition) COMANDO
    FROM
    sys.all_objects AS sp
    LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
    LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
    WHERE
    smsp.definition like '%NOMEOBJETO%' OR  ssmsp.definition like '%NOMEOBJETO%'

    Se resolver para ti cria uma SP .

    • Marcado como Resposta New Civic quinta-feira, 17 de setembro de 2009 19:56
    quinta-feira, 17 de setembro de 2009 13:58
  • Caracas que maravilha!!!!

    é isto mesmo que eu precisava.. funcionou.. inclusive, você teve o cuidado de separar pelo tipo V ou P que entendo que é view e procedure..

    Muito obrigado mesmo.

    Puxa não acredito que funcionou.. já estava desistindo..

    Abraços
    New Civic

    quinta-feira, 17 de setembro de 2009 19:52