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:
