Frequently, developers and DBAs need to search databases for objects or data. If you’d ever searched for a database function that contains a specific table column or a variable name, or for a table that contains specific data, you would have found out that there’s no one click solution, such as Ctrl+F
As there is no out-of-the-box solution in SQL Server management Studio, nor Visual Studio, here are a couple of options you can use:
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, you must write new code
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 you can use. 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 you have to use the sys.triggers view 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, you need to change SQL. To be able to do that, you have to be familiar with the system object structure so you can modify them. 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.
If you’re not an experienced developer, you prefer a tested and error-free solution to searching SQL objects and data manually, and you’re 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:
- In SQL Server Management Studio or Visual Studio’s Main menu, click ApexSQL Search
Select the Text search option:
- In the Search text field, enter the data value you want to search for
- From the Database drop-down menu, select the database to search in
- In the Select objects to search tree, select the tables and views to search in, or leave them all checked
- 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:
Click the Find option. The grid will be populated with the database tables and views that contain the entered value:
- Click the ellipsis button in the Column value to see the found object details:
To search for objects:
- In SQL Server Management Studio or Visual Studio’s Main menu,from the ApexSQL menu, click ApexSQL Search.
Select the Object search option:
- In the Search text field, enter the text you want to search for (e.g. a variable name)
- From the Database drop-down menu, select the database to search in
- In the Objects drop-down list, select the object types to search in, or leave them all checked
- 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
- Click the Find option:
The grid will be populated with the database objects that contain the specified object.
- 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 your databases and find data and objects you need.
