How to quickly search for SQL database data and objects in SSMS

Frequently, developers and DBAs need to search databases for objects or data. If a database function that contains a specific table column or a variable name, or for a table that contains specific data is searched, the simple solution for finding those values, does not exists.

As there is no out-of-the-box solution in SQL Server management Studio, nor Visual Studio, here are a couple of options that can be used:

Searching for data in tables and views

Using SQL to search for specific data in all tables and all columns of a database is far from an optimal solution. There are various SQL scripts with different approaches that can be used to obtain this information, what they have in common is that they all use cursors and system objects:

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;

The drawbacks of this solution are: use of cursors, which are generally inefficient, high complexity, a lot of time needed for execution, even on small databases. Another disadvantage is that it can be used to search for text data only. To search for other data types, such as time and datetime, a new code must be written.

Searching for objects

Searching for a database object name or object definition is a bit easier than searching for specific text. There are several methods that can be used. However, all of these methods include querying system objects.

The following SQL examples search for the specified text – the @StartProductID variable – in stored procedures. When searching for objects in other database object types – functions, triggers, columns, etc., or in multiple database object types at the same time, the SQL shown above should be modified accordingly.

INFORMATION_SCHEMA.ROUTINES

Use SQL that queries the INFORMATION_SCHEMA.ROUTINES view to search for a specific parameter in all procedures. The INFORMATION_SCHEMA.ROUTINES view contains information about all stored procedures and functions in a database. The ROUTINE_DEFINITION column contains the source statements that created the function or stored procedure:

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

And the result is:

It is not recommended to use INFORMATION_SCHEMA views to search for object schemas stored in the ROUTINE_SCHEMA column. Use the sys.objects catalog view instead:

sys.syscomments view

Query the sys.syscomments view, which contains information about every stored procedure, view, rule, default, trigger, and CHECK and DEFAULT constraints in a database. The query checks for a specific text in the text column, which contains the object DDL:

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

The result is:

This method is not recommended because the sys.syscomments table will be removed in the future versions of SQL Server.

sys.sql_modules view

Query the sys.sql_modules view which contains the name, type and definition of every module in a database:

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

The results are the same as for the previous method:

Other sys schemaviews

Query sys.syscomments, sys.schemas and sys.objects views. The sys.schemas view contains a row for every database schema. The sys.objects view contains a row every user-defined, schema-scoped object in a database. Note that it doesn’t contain the triggers information, so the sys.triggers view need to be used to search for object names or object definitions in 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;

The returned results are:

The main disadvantage of these methods is that for every change in object types searched, the SQL code need to be changed. To be able to do that, the object structure system needs to be well known, so it can be modified. Searching in multiple object types, and adding additional search criteria, such as including/excluding object names and bodies, or defining the escape character, brings even more complexity to SQL, which is prone to mistakes without proper and time-consuming testing.

For an unexperienced developer, for those which prefer a tested and error-free solution to searching SQL objects and data manually and for those which are not familiar with system objects that hold DDL information about database objects, use ApexSQL Search.

ApexSQL Search is a SQL search add-in for SSMS and Visual Studio. It can search for text within database objects (including object names), data stored in tables and views (even encrypted ones) and repeat previous searches in a single click.

To search for data in tables and views:

1. In SQL Server Management Studio or Visual Studio’s menu, click ApexSQL Search
   
2. Click on the Text search command:

1. In the Search text field, enter the data value that needs to be searched
   
2. From the Database drop-down menu, select the database to search in
   
3. In the Select objects to search tree, select the tables and views to search in, or leave them all checked
   
4. Select whether to search in views, numeric, text type, uniqueidentifier and date columns, by selecting the corresponding check boxes, and whether to search for an exact match. If searching in date columns, specify the date format:

1. Click the Find button. The grid will be populated with the database tables and views that contain the entered value:

1. Click the ellipse button in the Column value to see the found object details:

To search for objects:

1. In SQL Server Management Studio or Visual Studio’s menu, from the ApexSQL menu, click ApexSQL Search.
   
2. Select the Object search command:

1. In the Search text field, enter the text that needs to be searched (e.g. a variable name)
   
2. From the Database drop-down menu, select the database to search in
   
3. In the Objects drop-down list, select the object types to search in, or leave them all checked
   
4. Select whether to search in object, column, index names, object bodies, system objects, by selecting the corresponding check boxes, whether to search for an exact match and which escape character to use
   
5. Click the Find button:

The grid will be populated with the database objects that contain the specified object.

1. Double-click the object in the Object search grid and it will be highlighted in the Object Explorer:

SQL Server Management Studio and Visual Studio don’t provide search options for a database object name, object definition and data. SQL queries that search for these are complex, slow and require knowledge of SQL Server system objects. Use ApexSQL Search to dig through databases and finds needed data and objects.