Find a text in Database, Find string in database/all tables in SQL server

Below Query to search text in available procedures

SELECT name

FROM   sys.procedures

WHERE  Object_definition(object_id) LIKE '%MY_Search_String%'

This Procedure create a temp table and looking for searched string into all available tables

CREATE PROCEDURE usp_find_string_intables (@string AS VARCHAR(1000))
AS
BEGIN
	DECLARE @mincounter AS INT
	DECLARE @maxcounter AS INT
	DECLARE @stmtquery AS VARCHAR(1000)

	SET @stmtquery = ''

	CREATE TABLE #tmp (
		tablename VARCHAR(128)
		,columnname VARCHAR(128)
		,rowid INT identity
		)

	CREATE TABLE #tablelist (
		tablename VARCHAR(128)
		,columnname VARCHAR(128)
		)

	DECLARE @tmp TABLE (name VARCHAR(128))
	DECLARE @tablename AS VARCHAR(128)
	DECLARE @columnname AS VARCHAR(128)

	INSERT INTO #tmp (
		tablename
		,columnname
		)
	SELECT a.name
		,b.name AS columnname
	FROM sysobjects a
	INNER JOIN syscolumns b ON a.name = object_name(b.id)
	WHERE a.type = 'u'
		AND b.xtype IN (
			SELECT xtype
			FROM systypes
			WHERE name = 'text'
				OR name = 'ntext'
				OR name = 'varchar'
				OR name = 'nvarchar'
				OR name = 'char'
				OR name = 'nchar'
			)
	ORDER BY a.name

	SELECT @maxcounter = max(rowid)
		,@mincounter = min(rowid)
	FROM #tmp

	WHILE (@mincounter <= @maxcounter)
	BEGIN
		SELECT @tablename = tablename
			,@columnname = columnname
		FROM #tmp
		WHERE rowid = @mincounter

		SET @stmtquery = 'select top 1  ' + '[' + @columnname + ']' + ' from ' +

                     '[' + @tablename + ']' + ' where ' + '[' + @columnname + ']' +

                     ' like ' + '''%' + @string + '%''';

		INSERT INTO @tmp (name)
		EXEC (@stmtquery)

		IF @@rowcount > 0
			INSERT INTO #tablelist
			VALUES (
				@tablename
				,@columnname
				)

		SET @mincounter = @mincounter + 1
	END

	SELECT *
	FROM #tablelist
END

FInd String in Database 

EXEC usp_find_string_intables 'Search Sring Place here'