--List All Instance Tables, Stored Procedures, Views and Functions
EXEC dbo.SearchObjectDefinition

--List All Stored Procedures, and Functions in the Databases 
--TestDatabaseOne and TestDatabaseTwo
EXEC dbo.SearchObjectDefinition @ObjectType = 'Sp, Fn'
	,@DatabaseName = 'TestDatabaseOne, TestDatabaseTwo'

--List All Instance Tables, Stored Procedures, Views and Functions 
--where Object Definition contains the word Insert
EXEC dbo.SearchObjectDefinition @strFind = 'insert'

--List All Instance Tables where Object Name is Customers and 
--Column name contains the word Phone
EXEC dbo.SearchObjectDefinition @ObjectType = 'tb'
	,@ObjectName = 'Customers'
	,@strFind = 'Phone'

--USE [DatabaseName];
--GO

IF OBJECT_ID('[Split]') IS NULL
	EXEC ('CREATE FUNCTION dbo.[Split](@i INT) RETURNS @RtnValue TABLE (j INT) AS BEGIN INSERT INTO @RtnValue (j) SELECT 1 RETURN END');
GO

ALTER FUNCTION [dbo].[Split] (
	@List NVARCHAR(2000)
	,@SplitOn NVARCHAR(5)
	)
RETURNS @RtnValue TABLE (
	Id INT identity(1, 1)
	,Value NVARCHAR(100)
	)
AS
BEGIN
	WHILE (Charindex(@SplitOn, @List) > 0)
	BEGIN
		INSERT INTO @RtnValue (value)
		SELECT Value = ltrim(rtrim(Substring(@List, 1, Charindex(@SplitOn, @List) - 1)))

		SET @List = Substring(@List, Charindex(@SplitOn, @List) + len(@SplitOn), len(@List))
	END

	INSERT INTO @RtnValue (Value)
	SELECT Value = ltrim(rtrim(@List))

	RETURN
END

--USE [DatabaseName];
--GO

IF OBJECT_ID('[SearchObjectDefinition]') IS NULL
	EXEC ('CREATE PROCEDURE dbo.[SearchObjectDefinition] AS SELECT 1')
GO

ALTER PROCEDURE [dbo].[SearchObjectDefinition] (
	@ObjectType AS VARCHAR(20) = NULL
	,@ObjectName AS SYSNAME = NULL
	,@DatabaseName AS SYSNAME = NULL
	,@strFind AS VARCHAR(MAX) = NULL
	)
AS
BEGIN
	SET NOCOUNT ON;
	SET @strFind = ISNULL(@strFind, '')
	SET @ObjectName = ISNULL(@ObjectName, '')

	IF OBJECT_ID('tempdb..#Result') IS NOT NULL
		DROP TABLE #Result;

	DECLARE @DatabaseTable TABLE (DbName SYSNAME)
	DECLARE @DbName AS SYSNAME
	DECLARE @Sql AS VARCHAR(MAX)

	CREATE TABLE #Result (
		DbName SYSNAME NULL
		,ObjectType VARCHAR(2)
		,ObjectName SYSNAME
		,ObjectDefinition VARCHAR(MAX)
		)

	IF @DatabaseName IS NOT NULL
	BEGIN
		INSERT INTO @DatabaseTable (DbName)
		SELECT Value
		FROM dbo.Split(@DatabaseName, ',')
	END

	IF @DatabaseName IS NULL
	BEGIN
		INSERT INTO @DatabaseTable (DbName)
		SELECT NAME
		FROM master.dbo.sysdatabases
		WHERE NAME NOT IN (
				'tempdb'
				,'master'
				,'msdb'
				,'model'
				)
		ORDER BY NAME ASC
	END

	SET @DbName = ''

	--TO FIND STRING IN ALL PROCEDURES  
	IF @ObjectType LIKE '%Sp%'
		OR @ObjectType IS NULL
	BEGIN
		WHILE @DbName IS NOT NULL
		BEGIN
			SET @DbName = (
					SELECT MIN(DbName)
					FROM @DatabaseTable
					WHERE DbName > @DbName
					)
			SET @Sql = '
			USE ' + QUOTENAME(@DbName) + ';
			
			INSERT INTO #Result (
				DbName
				,ObjectType
				,ObjectName
				,ObjectDefinition
				)
			SELECT ''' + @DbName + ''' AS DbName
				,''Sp'' AS ObjectType
				,OBJECT_NAME(OBJECT_ID) AS ObjectName
				,OBJECT_DEFINITION(OBJECT_ID) AS ObjectDefinition
			FROM ' + QUOTENAME(@DbName) + '.sys.procedures
			WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ''%'' + ''' + @strFind + ''' + ''%''
			AND Name LIKE ''%'' + ''' + @ObjectName + ''' + ''%''
			'

			EXEC (@Sql)
		END
	END

	SET @DbName = ''

	--TO FIND STRING IN ALL VIEWS   
	IF @ObjectType LIKE '%Vw%'
		OR @ObjectType IS NULL
	BEGIN
		WHILE @DbName IS NOT NULL
		BEGIN
			SET @DbName = (
					SELECT MIN(DbName)
					FROM @DatabaseTable
					WHERE DbName > @DbName
					)
			SET @Sql = '
		USE ' + QUOTENAME(@DbName) + ';	
		
		INSERT INTO #Result (
			DbName
			,ObjectType
			,ObjectName
			,ObjectDefinition
			)
		SELECT ''' + @DbName + ''' AS DbName
			,''Vw'' AS ObjectType
			,OBJECT_NAME(OBJECT_ID) AS ObjectName
			,OBJECT_DEFINITION(OBJECT_ID) AS ObjectDefinition
		FROM ' + QUOTENAME(@DbName) + '.sys.VIEWS
		WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ''%'' + ''' + @strFind + ''' + ''%''
		AND Name LIKE ''%'' + ''' + @ObjectName + ''' + ''%''
			'

			EXEC (@Sql)
		END
	END

	SET @DbName = ''

	--TO FIND STRING IN ALL FUNCTION 
	IF @ObjectType LIKE '%Fn%'
		OR @ObjectType IS NULL
	BEGIN
		WHILE @DbName IS NOT NULL
		BEGIN
			SET @DbName = (
					SELECT MIN(DbName)
					FROM @DatabaseTable
					WHERE DbName > @DbName
					)
			SET @Sql = '
		USE ' + QUOTENAME(@DbName) + ';	
		
		INSERT INTO #Result (
			DbName
			,ObjectType
			,ObjectName
			,ObjectDefinition
			)
		SELECT ''' + @DbName + ''' AS DbName
			,''Fn'' AS ObjectType
			,ROUTINE_NAME AS ObjectName
			,ROUTINE_DEFINITION AS ObjectDefinition
		FROM ' + QUOTENAME(@DbName) + '.INFORMATION_SCHEMA.ROUTINES
		WHERE ROUTINE_DEFINITION LIKE ''%'' + ''' + @strFind + ''' + ''%''
			AND ROUTINE_NAME LIKE ''%'' + ''' + @ObjectName + ''' + ''%''
			AND ROUTINE_TYPE = ''FUNCTION''
		ORDER BY ROUTINE_NAME
			'

			EXEC (@Sql)
		END
	END

	SET @DbName = ''

	--TO FIND STRING IN ALL TABLES OF DATABASE.  
	IF @ObjectType LIKE '%Tb%'
		OR @ObjectType IS NULL
	BEGIN
		WHILE @DbName IS NOT NULL
		BEGIN
			SET @DbName = (
					SELECT MIN(DbName)
					FROM @DatabaseTable
					WHERE DbName > @DbName
					)
			SET @Sql = '
		USE ' + QUOTENAME(@DbName) + ';	
			
		INSERT INTO #Result (
			DbName
			,ObjectType
			,ObjectName
			,ObjectDefinition
			)
		SELECT ''' + @DbName + ''' AS DbName
			,''Tb'' AS ObjectType
			,t.NAME AS ObjectName
			,c.NAME AS ObjectDefinition
		FROM ' + QUOTENAME(@DbName) + '.sys.tables AS t
		INNER JOIN ' + QUOTENAME(@DbName) + '.sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
		WHERE c.NAME LIKE ''%'' + ''' + @strFind + ''' + ''%''
		AND t.Name LIKE ''%'' + ''' + @ObjectName + ''' + ''%''
		ORDER BY [ObjectDefinition] ASC
			'

			EXEC (@Sql)
		END
	END

	SELECT DbName
		,ObjectType
		,ObjectName
		,ObjectDefinition
	FROM #Result
	ORDER BY DbName ASC
		,ObjectType ASC
		,ObjectName ASC

	DROP TABLE #Result
END