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

About John

Software developer, lately specialising in Full Stack Web Development using c#, ASP.Net WebAPI 2, and Angular.
This entry was posted in Development and tagged , . Bookmark the permalink.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s