Share this post

This function search a word in all the sysobjects and return the result with the source (storedprocedure, table, ...).

Thanks to my colleague Nicolas Pecqueux for this script, I've juste adapted it.

CREATE Function [dbo].[udf_Find] (@string varchar(100))
RETURNS @retTable TABLE 
(
    NameObject nvarchar(128) NULL,
    ObjectType nvarchar(100) NULL,
    NameParent nvarchar(128) NULL,
    ObjectTypeParent nvarchar(100) NULL,
    DataBaseName nvarchar(128) NULL
)
AS
BEGIN
INSERT INTO @retTable(NameObject, ObjectType, NameParent, ObjectTypeParent, DataBaseName) 
SELECT	s.[Name] AS [Name],
		ObjectType =	CASE s.Type
							when 'D' then 'Default or DEFAULT constraint' 
							when 'FN' then 'Scalar function' 
							when 'IF' then 'Inlined table-function' 
							when 'P' then 'Stored procedure' 
							when 'R' then 'Rule' 
							when 'RF' then 'Replication filter stored procedure' 
							when 'TF' then 'Table function' 
							when 'TR' then 'Trigger' 
							when 'U' then 'User table' 
							when 'V' then 'View' 
							when 'X' then 'Extended stored procedure'
							when 'PK' then 'Primary Key'
							when 'F' then 'Foreign Key'
							else 'others'
						END,
		s2.Name AS [Name Parent],
		ObjectTypeParent =	CASE IsNull(s2.Type,'')
								when 'D' then 'Default or DEFAULT constraint' 
								when 'FN' then 'Scalar function' 
								when 'IF' then 'Inlined table-function' 
								when 'P' then 'Stored procedure' 
								when 'R' then 'Rule' 
								when 'RF' then 'Replication filter stored procedure' 
								when 'TF' then 'Table function' 
								when 'TR' then 'Trigger' 
								when 'U' then 'User table' 
								when 'V' then 'View' 
								when 'X' then 'Extended stored procedure'
								when 'PK' then 'Primary Key'
								when 'F' then 'Foreign Key'
								WHEN '' THEN null
								else 'others'
							END,
		(Select TOP 1 TABLE_CATALOG From Information_schema.Tables) AS DataBaseName
	FROM sysobjects s
	LEFT JOIN sysobjects s2 ON s2.id = s.parent_obj
	WHERE	s.id IN (SELECT id FROM syscomments where [text] LIKE '%'+@string+'%')
		OR	s.id IN (SELECT object_id FROM sys.all_columns WHERE [name] LIKE '%'+@string+'%')
		OR	s.id IN (SELECT parent_obj FROM [sysobjects] WHERE [name] LIKE '%'+@string+'%')
		OR	s.name LIKE '%'+@string+'%'
UNION
	SELECT	sjs.[step_name] COLLATE Latin1_General_CI_AS AS [Name], 
			ObjectType = 'Job Step SQL',
			sj.[name] COLLATE Latin1_General_CI_AS AS [Name Parent],
			ObjectTypeParent = 'Job SQL',
			sjs.database_name COLLATE Latin1_General_CI_AS AS DataBaseName
	FROM msdb.dbo.sysjobs sj
	LEFT JOIN msdb.dbo.[sysjobsteps] sjs ON sj.[job_id] = sjs.[job_id]
	WHERE	sjs.[command] LIKE '%'+@string+'%'
		OR	sjs.[step_name] LIKE '%'+@string+'%'

RETURN
END

Example and result :

SELECT * FROM dbo.udf_Find('Theme')