Some times we need to search any data and we don't know this question answer "What is the table and column contains this value?" We must search string in all table and columns(char,nchar,varchar,nvarchar,text and ntext data types). In this senario we search string for each table and column. In this case lets use the cursors. More informations for cursors click here. 

Lets coding... We need to declare following variables:

DECLARE @TmpSearchString NVARCHAR(MAX) -- Search keyword
DECLARE @TmpTableName NVARCHAR(MAX) -- Temporary table name holder
DECLARE @ObjectID BIGINT -- Temporary object id
DECLARE @ObjectName nvarchar(255) -- Temporary object name
DECLARE @tblResult TABLE( -- Result table
TableName nvarchar(255),
ColumnName NVARCHAR(255),
ColumnValue nvarchar(MAX)
)

My idea is this process runs in stored procedure, so i declared procedure is bellow:

CREATE PROC FindString(@SearchString NVARCHAR(255))
AS
BEGIN

END
GO

I have written description of the required line by line.

CREATE PROC FindString(@SearchString NVARCHAR(255))
AS
BEGIN
SET NOCOUNT ON;

DECLARE @TmpSearchString NVARCHAR(MAX) -- Search keyword
DECLARE @TmpTableName NVARCHAR(MAX) -- Temporary table name holder
DECLARE @ObjectID BIGINT -- Temporary object id
DECLARE @ObjectName nvarchar(255) -- Temporary object name
DECLARE @tblResult TABLE( -- Result table
	TableName nvarchar(255),
	ColumnName NVARCHAR(255),
	ColumnValue nvarchar(MAX)
)

DECLARE myCursor CURSOR FOR
SELECT object_id, name FROM sys.columns -- Select all columns used in user defined tables
WHERE object_id IN (
	SELECT object_id FROM sys.tables WHERE type='U' -- Only user defined tables
) AND system_type_id IN (35,99,167,175,231,239)


OPEN myCursor -- Opening cursor

FETCH NEXT FROM myCursor -- Select next @ObjectID and @ObjectName from next record in myCursor
INTO @ObjectID, @ObjectName

WHILE @@FETCH_STATUS = 0 -- If next record exists
BEGIN
	-- Set table name to @TmpTableName variable
	SELECT @TmpTableName = Name FROM sys.tables WHERE object_id = @ObjectID

	/*
	* Creating search string with @TmpTableName,@ObjectName and @SearchString
	* For example: Select 'Tutorial','Name',Name From Tutorial WHERE CAST(Name as nvarchar(max)) LIKE '%lue%'
	*/
	SET @TmpSearchString =	' Select ' + '''' + @TmpTableName + ''',''' + @ObjectName + ''',' + @ObjectName + ' ' + 
	' From ' + @TmpTableName +
	' WHERE CAST(' + @ObjectName + ' AS NVARCHAR(MAX)) LIKE ''' + @SearchString + ''''
	
	-- Execute dynamic select query and insert result to @tblResult table
	INSERT INTO @tblResult
	EXEC sp_executesql @TmpSearchString
	
	-- Fetch next record
	FETCH NEXT FROM myCursor
	INTO @ObjectID, @ObjectName
	
END -- WHILE @@FETCH_STATUS = 0
	
CLOSE myCursor; -- Closing cursor
DEALLOCATE myCursor; -- Deallocating cursor
	
SELECT * FROM @tblResult -- Selecting all search results from @tblResut

END

And lets run this procedure to see results

EXEC FindString '%lue%'

Result: