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.
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:
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.
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:
In SQL Server Management Studio or Visual Studio’s menu, click ApexSQL Search
- Click on the Text search command:
In the Search text field, enter the data value that needs to be searched
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 button. The grid will be populated with the database tables and views that contain the entered value:
- Click the ellipse button in the Column value to see the found object details:
To search for objects:
In SQL Server Management Studio or Visual Studio’s menu, from the ApexSQL menu, click ApexSQL Search.
- Select the Object search command:
In the Search text field, enter the text that needs to be searched (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 button:
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 databases and finds needed data and objects.
April 4, 2013