A stored procedure to search database objects (triggers, scalar functions, table valued functions, views and stored procedures) for a user defined string.
IF OBJECT_ID (N'dbo.sp_SearchDBObjects', N'P') IS NOT NULL
DROP PROCEDURE sp_SearchDBObjects;
CREATE PROCEDURE dbo.sp_SearchDBObjects (@String varchar(max))
CASE WHEN O.TYPE = 'TR' THEN 'Trigger'
WHEN O.TYPE = 'FN' THEN 'Scalar Function'
WHEN O.TYPE = 'IF' THEN 'Table Valued Function'
WHEN O.TYPE = 'V' THEN 'View'
WHEN O.TYPE = 'P' THEN 'Stored Procedure'
ELSE NULL END AS [Object Type],
S.NAME + '.' + O.NAME AS [Object Name],
M.DEFINITION AS [Object Code]
SYS.SQL_MODULES AS m
INNER JOIN SYS.OBJECTS AS O ON M.OBJECT_ID = O.OBJECT_ID
INNER JOIN SYS.SCHEMAS AS S ON O.SCHEMA_ID = S.SCHEMA_ID
('TR', 'FN', 'IF', 'V', 'P')
M.DEFINITION LIKE '%' + @String + '%'
The script searches through every column of every record in a database to find a value. It works with numbers too. Simply assign a value to @MyString. Indicate what datatype(s) you wish to search through, and indicate if you want wildcards, and execute. The search is smart and will only search through columns where @MyString is […]
Problem The usage of a collection of integers in some scenarios is considered to be optimal because of the overhead involved by the creation of another user table used just to store numbers, which is the most common solution. What are collections of integers? They are lists or arrays consisting of integers. The number of […]