Como agilizar a busca de dados e objetos no banco de dados SQL Server

Frequentemente, desenvolvedores e DBAs precisam buscar dados ou objetos em um banco de dados. Se você já buscou por funções em um banco de dados que contém uma coluna de tabela específica ou um nome variável, ou por uma tabela que contém um dado específico, você teria descoberto que não há uma solução de um click, como um Crtl+F.

Com não há uma solução fora da caixa no SQL Server Management Studio, nem no Visual Studio, aqui estão algumas opções que você pode usar:

Buscar por um dado em tabelas e visualizações

Usar o SQL para buscar um dado específico em todas as tabelas e todas as colunas em um banco de dados está longe de ser uma solução perfeita. Há vários scripts em SQL com diferentes abordagens que podem ser usados para obter esta informação, o que eles tem em comum é que todos eles usam cursores e objetos de sistema.

DECLARE
   @SearchText varchar(200),
   @Table varchar(100),
   @TableID int,
   @ColumnName varchar(100),
   @String varchar(1000);
--modify the variable, specify the text to search for SET @SearchText = 'John';
DECLARE CursorSearch CURSOR
    FOR SELECT name, object_id
        FROM sys.objects
      WHERE type = 'U';
--list of tables in the current database. Type = 'U' = tables(user-defined) OPEN CursorSearch;
FETCH NEXT FROM CursorSearch INTO @Table, @TableID;
WHILE
       @@FETCH_STATUS
       =
       0
    BEGIN
        DECLARE CursorColumns CURSOR
            FOR SELECT name
                  FROM sys.columns
                WHERE
                       object_id
                       =
                       @TableID AND system_type_id IN(167, 175, 231, 239);
        -- the columns that can contain textual data        
--167 = varchar; 175 = char; 231 = nvarchar; 239 = nchar        
OPEN CursorColumns;
        FETCH NEXT FROM CursorColumns INTO @ColumnName;
        WHILE
               @@FETCH_STATUS
               =
               0
            BEGIN
                SET @String = 'IF EXISTS (SELECT * FROM '
                            + @Table
                            + ' WHERE '
                            + @ColumnName
                            + ' LIKE ''%'
                            + @SearchText
                            + '%'') PRINT '''
                            + @Table
                            + ', '
                            + @ColumnName
                            + '''';
                EXECUTE (@String);
                FETCH NEXT FROM CursorColumns INTO @ColumnName;
            END;
        CLOSE CursorColumns;
        DEALLOCATE CursorColumns;
        FETCH NEXT FROM CursorSearch INTO @Table, @TableID;
    END;
CLOSE CursorSearch;
DEALLOCATE CursorSearch;

A desvantagem dessa solução é: fazer uso de cursores, na qual são geralmente ineficientes, alta complexidade, é preciso ter muito tempo para a execução, mesmo em banco de dados pequenos. Outra desvantagem é que ele somente pode ser usado para buscar dados do tipo texto. Para buscar por outro tipo de dado, como data e hora, você deve escrever um novo código.

Busca por objetos

Buscar por um nome de objeto no banco de dados ou uma definição de objeto é um pouco mais fácil do que buscar por um texto específico. Há diversos métodos que você pode usar. Entretanto, todos estes métodos incluem consultas de objetos de sistema.

Os seguintes exemplos buscam por um texto específico no SQL – a variável @StartProductID – em uma stored procedure. Quando buscar por objetos em outro tipo de objeto de banco de dados – funções, triggers, colunas, etc., ou em multiplos tipos de objetos de banco de dados ao mesmo tempo, o SQL exibido acima mostra como devem ser alteradas em conformidade.

INFORMATION_SCHEMA.ROUTINES

Use as consultas SQL na view INFORMATION_SCHEMA.ROUTINES para buscar por um parâmetro específico em todas as procedures. A view INFORMATION_SCHEMA.ROUTINES contêm informações sobre todas as stored procedures e funções em um banco de dados. A coluna ROUTINE_DEFINITION contêm declarações de fontes que criaram as funções ou stored procedures.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%@StartproductID%' 
    AND ROUTINE_TYPE='PROCEDURE'

E o resultado é

Isto não é recomentado se usar a view INFORMATION_SCHEMA para buscar esquemas de objetos armazenados em colunas ROUTINE_SCHEMA. Em seu lugar, use o catálogo sys.objects para visualizar.

view sys.syscomments

Consultar a view sys.syscomments, na qual contém informações sobre todas as stored procedures, views, regras, padrões, trigger e restrições CHECK e DEFAULT em um banco de ados. As consultas verificam um texto em uma coluna texto, que contêm o objeto DLL

SELECT OBJECT_NAME( id )
  FROM SYSCOMMENTS
  WHERE text LIKE '%@StartProductID%' AND OBJECTPROPERTY(id , 'IsProcedure') = 1
  GROUP BY OBJECT_NAME( id );

O resultado é

Este método não é recomendado porque a tabela sys.syscomments será removida em uma futura versão do SQL Server.

sys.sql_modules view

Consulte a view sys.sql_modules, na qual contêm nomes, tipos e definições de todos os módulos no banco de dados.

SELECT OBJECT_NAME( object_id )
  FROM sys.sql_modules
WHERE
       OBJECTPROPERTY(object_id , 'IsProcedure')
       =
       1 AND definition LIKE '%@StartProductID%';

Os resultados são os mesmos dos métodos anteriores.

Outros sys schemaviews

Consultar as views sys.syscomments, sys.schemas e sys.objects. A view sys.schemas contêm linhas para todos os esquemas do banco de ados. A view sys.objects contêm todas as linhas de objetos user-defined, schema-scoped de um banco de ados. Note que estes não contêm as informações de triggers, então você tem que usar a view sys.triggers para buscar por nomes de objetos ou definições de objetos em triggers.

DECLARE
 @searchString nvarchar( 50 );
SET@searchString = '@StartProductID';
SELECT DISTINCT
    s.name AS Schema_Name , O.name AS Object_Name , C.text AS Object_Definition
FROM
     syscomments C INNER JOIN sys.objects O
                     ON
     C.id
     =
     O.object_id
                   INNER JOIN sys.schemas S
                   ON
     O.schema_id
     =
     S.schema_id
WHERE
    C.text LIKE
     '%'
   + @searchString
   + '%'
 OR O.name LIKE
     '%'
   + @searchString
   + '%'
ORDER BY
       Schema_name , Object_name;

Os resultados gerados são:

A principal desvantagem destes métodos é que para toda a alteração em um tipo de objeto encontrado, você precisa alterar o SQL. Para realizar isso, você precisa estar familiarizado com o a estrutura do sistema de objeto que você pode modificar. Realizar uma busca em múltiplos tipos de objetos, e acrescentar critérios adicionais em buscas, como incluir/excluir nomes e objetos e corpos, ou definir caracteres de escape, trazendo inclusive informações mais complexas para o SQL, na qual é propenso à erros sem estar adequado e testando o tempo-consumo.

Se você não é um desenvolvedor experiente, e você prefere uma solução testada e sem erros para buscar objetos e dados manualmente no SQL, e você não está familiarizado com objetos de sistema que mantém informações DDL sobre objetos de banco de dados, use o ApexSQL Search.

O ApexSQL Search é um adicional de pesquisa SQL para SSMS e Visual Studio. Este pode buscar por textos dentro de objetos no banco de dados (incluindo nomes de objetos), dados armazenados em tabelas e views (mesmo aqueles encriptados), e repetir buscas prévias em um simples clique.

Para buscar por dados em tabelas e views:

1. No menu principal em SQL Server Management Studio ou Visual Studio’s, clique ApexSQL Search

2. Selecione a opção Database text search…:

   

3. No campo Search, digite o valor que você quer buscar
4. No menu suspenso Database, selecione o banco de dados em que irá buscar
5. Na árvore Select objects to search, selecione as tabelas e visualizações para buscar ou deixe-os todos marcados
6. Selecione se quer buscar em view, numérico, tipo de texto, identificadores únicos ou colunas de datas, selecionando as caixas correspondentes, e se quer buscar por um termo exato. Se buscar em colunas de datas, especifique o formato de data:
   

   

7. Clique o opção Find now. Será exibida uma grade populada com as tabelas e visualizções das tabelas que contém os valores inseridos:

   

8. Clique o botão Column value para vizualiar os detalhes do objeto encontrado:
   

   

Para buscar por objetos:

1. No menu principal em SQL Server Management Studio ou Visual Studio, para o menu do ApexSQL, clique em ApexSQL Search.

2. Selecione a opção Database object search…:

   

3. No campo Searche text, digite o texto que você que buscar (exemplo: um nome de variável)
4. No menu drop-dows do Database, selecione o banco de dados para a busca
5. Na lista do menu drop-down Objects, selecione os tipos de objetos para busca, ou deixe todos marcados
6. Selecione se quer buscar um objeto, coluna, nomes de index, corpos de objetos, objetos de sistema, selecionando a caixa correspondente, se quiser buscar por um termo exato e qual caracter de escape usar.
7. Clique a opção Find now:
   

   

   A grade será populada com objetos do banco de dados contendoo o objeto específico.

8. Dê duplo clique no objeto na grade Database object search, e este será ressaltado no Object Explorer
   

   

O SQL Server Management Studio e Visual Studio não fornecem opções de buscas para um nome de objeto de banco de dados, definição de objetos e dados. As consultas do SQL que realizam essas buscas são complexas, lentas e requerem conhecimentos em objetos de sistemas do SQL Server. Use o ApexSQL Search para realizar buscas em seus bancos de ados e encontrar dados e objetos que você precisa.

Tradução: Ricardo Leka Roveri