SQL Server – searching all columns and tables

I’ve inherited a legacy app at work, the developer having gone on to pastures new. The handover was a little light in details especially in the area of the database. Of course, the first bit of work that comes in requires a change to a value that’s held in different columns in different tables in the database, and I have to find them.

Therefore with a little bit of work I came up with this:

DECLARE @tempTable TABLE(rowId INT IDENTITY(1,1), tableName sysName, columnName sysName, searchSql VARCHAR(2000), dataFound BIT)
DECLARE @parameter NVARCHAR(2000)
DECLARE @searchString varchar(2000)

set @searchString = 'Text To Find'

INSERT INTO @tempTable(tablename, columnName, searchSql)
SELECT      table_name, 
            'select @DataExists=1 from [' + table_name + '] where [' + column_name + '] like ''%' +@searchString + '%'''
  FROM information_schema.columns
WHERE data_type = 'varchar'
  ORDER BY table_name, column_name
SELECT @i=1, @max = MAX(rowId)
FROM @tempTable  
SET @parameter = '@DataExists Bit OUTPUT'

WHILE @i < @max
      SELECT @sql = searchSql 
        FROM @tempTable 
       WHERE rowId = @i
       set @dataExists = 0
       EXEC sp_executeSql @sql, @parameter, @DataExists = @DataExists OUTPUT
      IF @DataExists = 1
            UPDATE @tempTable SET DataFound = 1 WHERE rowId = @i
      SET @i = @i + 1

SELECT distinct tablename, columnname
  FROM @temptable 
 WHERE datafound =1

Microsoft ALM

I was very impressed overall by the latest product suite and the improvements to our workflow it would enable.

While we as the web development team already use TFS for version control, and are starting to look at it for user stories and task tracking, the possibilities of having it manage all the complete workflow from requirements and specifications, through the development process, then testing, and finally the release process. Keeping the requirements linked to the tasks, the source code, and the tests.

┬áLet’s hope we can start using this fully in the near future.