Purpose:
The [util].[SearchAll] stored procedure searches for a specified string (@strFind) in the following database objects:
Procedures
Views
Functions
Table Columns
Parameters:
@strFind: The string to search for within the database objects. Type:VARCHAR(MAX)
CREATE PROCEDURE [util].[SearchAll]
(@strFind AS VARCHAR(MAX))
AS
BEGIN
SET NOCOUNT ON;
--TO FIND STRING IN ALL PROCEDURES
BEGIN
SELECT OBJECT_NAME(OBJECT_ID) SP_Name
,OBJECT_DEFINITION(OBJECT_ID) SP_Definition
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@strFind+'%'
END
--TO FIND STRING IN ALL VIEWS
BEGIN
SELECT OBJECT_NAME(OBJECT_ID) View_Name
,OBJECT_DEFINITION(OBJECT_ID) View_Definition
FROM sys.views
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@strFind+'%'
END
--TO FIND STRING IN ALL FUNCTION
BEGIN
SELECT ROUTINE_NAME Function_Name
,ROUTINE_DEFINITION Function_definition
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%'+@strFind+'%'
AND ROUTINE_TYPE = 'FUNCTION'
ORDER BY
ROUTINE_NAME
END
--TO FIND STRING IN ALL TABLES OF DATABASE.
BEGIN
SELECT t.name AS Table_Name
,c.name AS COLUMN_NAME
FROM sys.tables AS t
INNER JOIN sys.columns c
ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%'+@strFind+'%'
ORDER BY
Table_Name
END
END
Example Execution:
To search for the string username across all specified database objects,
execute the following command:
exec [util].[SearchAll] 'username'The [util].[SearchAll] stored procedure is designed to comprehensively
search for a specified string within the definitions of all
stored procedures, views, functions, and table columns in the
database. This is particularly useful for identifying where
specific terms or values are used across various database objects.
