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
Patel Mohamad