Searching for a Specific Text String in SQL Server Objects

When managing a SQL Server database, you may need to find occurrences of a specific text string within database objects, such as table names, stored procedures, functions, or views. This can be useful for debugging, auditing, or refactoring existing code.

The following stored procedure, sp_search_code, allows you to search for a specific text string across different database objects, including table names, stored procedures, functions, and views

CREATE PROC [dbo].[sp_search_code] (
	@SearchStr VARCHAR(100)
	,@RowsReturned INT = NULL OUT
	)
AS
BEGIN
	SET NOCOUNT ON

	SELECT DISTINCT USER_NAME(o.uid) + '.' 
    + OBJECT_NAME(c.id) AS 'Object name'
		,CASE 
			WHEN OBJECTPROPERTY(c.id, 'IsReplProc') = 1
				THEN 'Replication stored procedure'
			WHEN OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1
				THEN 'Extended stored procedure'
			WHEN OBJECTPROPERTY(c.id, 'IsProcedure') = 1
				THEN 'Stored Procedure'
			WHEN OBJECTPROPERTY(c.id, 'IsTrigger') = 1
				THEN 'Trigger'
			WHEN OBJECTPROPERTY(c.id, 'IsTableFunction') = 1
				THEN 'Table-valued function'
			WHEN OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1
				THEN 'Scalar-valued function'
			WHEN OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1
				THEN 'Inline function'
			END AS 'Object type'
		,'EXEC sp_helptext ''' + USER_NAME(o.uid) + '.' 
          + OBJECT_NAME(c.id) + '''' 
         AS 'Run this command to see the object text'
	FROM syscomments c
	INNER JOIN sysobjects o ON c.id = o.id
	WHERE c.TEXT LIKE '%' + @SearchStr + '%'
		AND encrypted = 0
		AND (
			OBJECTPROPERTY(c.id, 'IsReplProc') = 1
			OR OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1
			OR OBJECTPROPERTY(c.id, 'IsProcedure') = 1
			OR OBJECTPROPERTY(c.id, 'IsTrigger') = 1
			OR OBJECTPROPERTY(c.id, 'IsTableFunction') = 1
			OR OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1
			OR OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1
			)
	ORDER BY 'Object type'
		,'Object name'

	SET @RowsReturned = @@ROWCOUNT
END

How to Use the Stored Procedure

To execute this stored procedure and search for a specific text string, you can use the following SQL command:

EXEC sp_search_code 'Customer';

This command will return a list of tables, views, stored procedures, and functions containing the word Customer in their names or definitions.

This stored procedure is a powerful tool for searching database objects based on a specific text string. Whether you need to find references to a particular table or locate a function containing certain logic, this procedure simplifies the process. You can further enhance it by adding search capabilities for triggers, indexes, or column names as needed.