search a table from any items of that table

There are no smart way of doing this. You have 100 colmuns and the only way is to build a sql-query dynamically and use a LIKE to search for text. (if all columns are text?)

You need to achive:

SELECT * FROM Table WHERE
         Column1 LIKE '%query%'
      OR Column2 LIKE '%query%'
      OR Column3 LIKE '%query%'
and so on

You could query some system tables to list all columns and iterate through them and generate the sql you need, but that would be nasty.

To search in sql you need to set up some indexing for performance and there's a lot of things to think about before firing a query to a large database.

My tips is to either look for some 3rd party search engine tools like Lucene.net or similar. Or do the job writing the query by hand.

A third option is to generate a second table with 2 columns (id and text) holding the values from all the columns in the table you want to search, and then search in that column.
A few downsides is that it will take up some space in your database and you can't weight score for each column.

CREATE TABLE SearchTable(
  [Id] [int] NOT NULL,
  [Text] [varchar](max) NOT NULL -- you should index this column
) 

And then copy data from your first table:

INSERT INTO SearchTable 
SELECT Id, Column1 + ' ' + Column2 + ' ' + Column3 -- and so on
FROM OriginalTable

Then you can search SearchTable:

SELECT OriginalTable.* FROM OriginalTable
INNER JOIN SearchTable ON OriginalTable.Id = SearchTable.Id
WHERE SearchTable.Text LIKE '%query%'

This could be done dynamically "on the fly" using temp tables, but if theres a lot of data I would schedule this to run and do indexing at specific times.

wat should i specify in where clause..
Whatever is the column name and related search text!

WHERE @ColumnName = @ColumnSearchText

try to write you sp dynamically..

use dynamic query for this purpose

look this simple example:-

ALTER proc [dbo].[usp_SelectTableData]
 @FieldName varchar(50)=null
,@FldType int =0 -- 0 for numeric and 1 = string
,@TableName varchar(50)
,@SearchStr VARCHAR(200) = null
as
begin
declare @Qry nvarchar(max)
if @FldType=0
set @Qry='Select  '+ @FieldName + ' from '  + @TableName +' where  '+ @FieldName +' =' + @SearchStr + ' '
else
set @Qry='Select  '+ @FieldName + ' from '  + @TableName +' where  '+ @FieldName +' =''' + @SearchStr + ''' '

EXEC(@Qry)
end