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 @i INT
DECLARE @max INT
DECLARE @DataExists BIT
DECLARE @sql NVARCHAR(2000)
DECLARE @parameter NVARCHAR(2000)
DECLARE @searchString varchar(2000)

set @searchString = 'Text To Find'

INSERT INTO @tempTable(tablename, columnName, searchSql)
SELECT      table_name, 
            column_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
BEGIN
      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
      
END

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

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.