title: How to search all text fields in a DB for some substring with T-SQL
body: <p>I have a huge schema, with several hundreds of tables and several thousands of columns.  I'd know that a specific IP address is stored in this database in several places, but I'm not sure what table(s) or column(s) it is stored in.  Basically, I'm trying to find everywhere that this IP address is stored in the DB so I can update it to a new value in all those places.</p>
  
  <p>Here's my first crack at a T-SQL statement to print out the table and column name, and the value, for every text column in the database that has the substring 10.15.13 in it.</p>
  
  <p>Now, this works, sort of.  The problem is, when I execute it in Management Studio, the call to sp_executesql will actually return all the empty results from every query that returns nothing (i.e. the column doesn't have any records with that substring), and it fills the result window to its max, and then I don't actually see if anything was printed.</p>
  
  <p>Is there a better way to write this query?  Or can I run it in some different way so that it only shows me the Tables and Columns where this substring exists?</p>
  
  <pre><code>DECLARE
      @SchemaName VARCHAR(50),
      @TableName VARCHAR(50),
      @ColumnName VARCHAR(50);
  BEGIN
      DECLARE textColumns CURSOR FOR
      SELECT s.Name, tab.Name, c.Name
      FROM Sys.Columns c, Sys.Types t, Sys.Tables tab, Sys.Schemas s
      WHERE s.schema_id = tab.schema_id AND tab.object_id = c.object_id AND c.user_type_id = t.user_type_id
      AND t.Name in ('TEXT','NTEXT','VARCHAR','CHAR','NVARCHAR','NCHAR');
  
      OPEN textColumns
  
      FETCH NEXT FROM textColumns
      INTO @SchemaName, @TableName, @ColumnName
  
      WHILE @@FETCH_STATUS = 0
      BEGIN
          DECLARE @sql NVARCHAR(MAX),
                  @ParamDef NVARCHAR(MAX),
                  @result NVARCHAR(MAX);              
          SET @sql = N'SELECT ' + @ColumnName + ' FROM ' + @SchemaName + '.' + @TableName + ' WHERE ' + @ColumnName + ' LIKE ''%10.15.13%''';
          SET @ParamDef = N'@resultOut NVARCHAR(MAX) OUTPUT';
  
          EXEC sp_executesql @sql, @ParamDef, @resultOut = @result OUTPUT;
  
          PRINT 'Column = ' + @TableName + '.' + @ColumnName + ', Value = ' + @result;
          FETCH NEXT FROM textColumns
          INTO @SchemaName, @TableName, @ColumnName       
      END
      CLOSE textColumns;
      DEALLOCATE textColumns;
  END
  </code></pre>
  
  <p>I'd like to see results something like this where it shows the table/column that the substring was found in, and the full value in that column...</p>
  
  <pre><code>Column = SomeTable.SomeTextColumn, Value = 'https://10.15.13.210/foo'
  Column = SomeTable.SomeOtherColumn, Value = '10.15.13.210'
  </code></pre>
  
  <p>etc.</p>