the following code illustrate you in breif actual i want the table name, respective column data from over all Tables of the Database

DECLARE @iIndexRow INT ,@RowsCount_Row int , @sqlquery NVARCHAR(4000)

DECLARE @Value VARCHAR(100)

DECLARE @TableName VARCHAR(100) ,@ColumnName VARCHAR(100)

DECLARE @xtype int

DECLARE @ID_Row INT

DECLARE @iIndexCol INT ,@RowsCount_Col int

SELECT @Value = 'VALUE'

SELECT Identity(INT,1,1) AS Sr_No, id, Name INTO #Temp_Table_Row

FROM SYSOBJECTS

WHERE xtype = 'U' ORDER BY [Name]

SELECT @RowsCount_Row = @@RowCount

SELECT @iIndexRow = 1

WHILE @iIndexRow <= @RowsCount_Row

BEGIN --- 1.0

if EXISTS(SELECT Sr_No FROM #Temp_Table_Row WHERE Sr_No = @iIndexRow)

BEGIN -- 1.1

SELECT @ID_Row = ID FROM #Temp_Table_Row WHERE Sr_No = @iIndexRow

SELECT Identity(INT,1,1) AS Sr_No , id, Name , xtype INTO #Temp_Table_Col

FROM Syscolumns

where id = @ID_Row

and xtype in (35 ,98 ,165 ,167 ,173 ,175 ,231 ,231 ,239) -- ,99 For datatypes char varhcar etc

ORDER BY [Name]

SELECT @RowsCount_Col = @@RowCount

SELECT @iIndexCol = 1

WHILE @iIndexCol <= @RowsCount_Col

BEGIN --- 2.0

if EXISTS(SELECT Sr_No FROM #Temp_Table_Col WHERE Sr_No = @iIndexCol)

BEGIN--- 2.1

SELECT @TableName = Object_Name(ID) ,@ColumnName = Name , @xtype = xtype FROM #Temp_Table_Col WHERE Sr_No = @iIndexCol

SET @sqlquery = N'SELECT ' + CHAR(39) +@TableName + CHAR(39) + ' AS TableName,* FROM [' + @TableName + '] WITH (NOLOCK) WHERE [' + @ColumnName + '] like% ' + CHAR(39) + @Value + CHAR(39)

if EXISTS(SP_EXECUTESQL @sqlquery)

BEGIN--- 2.1.0

EXEC SP_EXECUTESQL @sqlquery

END--- 2.1.0

END--- 2.1

SELECT @iIndexCol = @iIndexCol + 1

END --- 2.0

DROP TABLE #Temp_Table_Col

END-- 1.1

SELECT @iIndexRow = @iIndexRow + 1

END --- 1.0

DROP TABLE #Temp_Table_Row