Try implementing the following procedure :

Create Procedure PR_SearchDatabase

@field NVarchar(400),

@input NVarchar(400)

As

Begin

Declare @tablename NVarchar(400), @sql NVarchar(Max)

Declare @table Table(Data NVarchar(max))

Declare TableName_Cursor Cursor LOCAL STATIC Forward_Only

For

Select Distinct a.name From sys.tables as a, sys.columns As b

Where a.name = OBJECT_NAME(b.object_id)

AND b.name = @field

Open TableName_Cursor

Fetch Next From TableName_Cursor Into @tablename

While(@@FETCH_STATUS = 0)

Begin

Set @sql = 'Select ' + @field + ' From ' + @tablename + ' Where ' + @field + ' LIKE ''%' + @input + '%'''

Insert Into @table

Execute(@sql)

Fetch Next From TableName_Cursor Into @tablename

End

Close TableName_Cursor

Deallocate TableName_Cursor

Select * From @table

End

Use it as a base and get a script or a procedure to work for your specific requirement.