SET NOCOUNT on;
DECLARE @sql VARCHAR (1024)
DECLARE @table VARCHAR (64)
DECLARE @column VARCHAR (64)
DECLARE @value VARCHAR (64)
Set @value = ' 14799059407 '
CREATE TABLE #t (
TableName VARCHAR (64),
ColumnName VARCHAR (64)
)
DECLARE TABLES CURSOR
For
SELECT O.name, C.name
From syscolumns C
INNER JOIN sysobjects o on c.id = o.id
WHERE o.type = ' U ' and C.xtype in (167, 175, 231, 239)
ORDER by O.name, C.name
OPEN TABLES
FETCH NEXT from TABLES
Into @table, @column
While @ @FETCH_STATUS = 0
BEGIN
SET @sql = ' IF EXISTS (SELECT NULL from [' + @table + '] '
SET @sql = @sql + ' WHERE RTRIM (LTRIM ([' + @column + ')]) ' like '% ' + @value + '% ') '
SET @sql = @sql + ' INSERT into #t VALUES (' + @table + ', ' "
SET @sql = @sql + @column + ') '
EXEC (@sql)
FETCH NEXT from TABLES
Into @table, @column
END
CLOSE TABLES
Deallocate TABLES
SELECT *
From #t
DROP TABLE #t
How SQL SERVER queries the tables and fields of a particular value in the entire database