How to find a row in the database without knowing the name of the table and column?

Question

SQL Server

The database in MS SQL SERVER 2008, it knows exactly the contents of some field-string, but I don’t know the name of the field, in which of the tables it is also unknown. Is it possible to find all records in all database tables?

Answer 1

DECLARE @L VARCHAR(100) = '%Mars%'
DECLARE @TB VARCHAR(100), @CL VARCHAR(100), @Q NVARCHAR(250)
DECLARE @R TABLE ( TableField VARCHAR(100), FieldValue VARCHAR(250) )
DECLARE AllTable CURSOR FOR
SELECT TABLE_NAME, COLUMN_NAME 
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE DATA_TYPE LIKE '%char'
OPEN AllTable
FETCH NEXT FROM AllTable INTO @TB, @CL
WHILE @@FETCH_STATUS = 0 BEGIN
  SET @Q = 'SELECT ''' + @TB + '.' + @CL + ''', ' + @CL 
         + ' FROM ' + @TB + ' WHERE ' + @CL + ' LIKE ''' + @L + ''''
  INSERT @R EXECUTE sp_executesql @Q
  FETCH NEXT FROM AllTable INTO @TB, @CL
END
CLOSE AllTable
DEALLOCATE AllTable
SELECT * FROM @R

Answer 2

This is enough to generate dynamic sql :

select  column_id, name, system_type_id, max_length  from sys.columns where object_id = OBJECT_ID('имя_таблицы')