Victor's Blog

Live as if you were to die tomorrow. Learn as if you were to live forever

NAVIGATION - SEARCH

Search a value from the whole database

If you ever want to search something from the database but you don't know which tables to search from, here is the query you need:

/****** Script for SelectTopNRows command from SSMS  ******/
SET NOCOUNT ON

DECLARE
   @TotalRows   int,
   @Counter     int,
   @TableName   varchar(50),
   @ColumnName  varchar(50),
   @FieldValue  varchar(250),
   @SQLCommand  nvarchar(1000),
   @ValueToFind varchar(100)


SET @ValueToFind = 'VAULE TO SEARCH'

DECLARE @MyTable table
   (  RowID      int IDENTITY,
      TableName  varchar(50),
      ColumnName varchar(50)
   )

CREATE TABLE #FoundTable
   (  RowID      int IDENTITY,
      Tablename  varchar(50)
   )

INSERT INTO @MyTable
   SELECT
      TABLE_NAME,
      COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ( 'char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext' )

SELECT
   @TotalRows = @@ROWCOUNT,
   @Counter   = 1

WHILE ( @Counter <= @TotalRows )
  
   BEGIN
 
      SELECT
         @TableName = TableName,
         @ColumnName = ColumnName
      FROM @MyTable
      WHERE RowID = @Counter
    
      SET @SQLCommand = 'IF EXISTS ( ' +
                        'SELECT 1 FROM [' + @TableName + '] WHERE [' + @ColumnName + '] LIKE ''%' + @ValueToFind + '%'' ' +
                        ' )' +
                        'INSERT INTO #FoundTable ' +
                        '   SELECT ''' + @TableName + '(' + @ColumnName + ')'''

      EXECUTE sp_executesql @SQLCommand
     
      SET @Counter = ( @Counter + 1 )
  
   END

SELECT * FROM #FoundTable

DROP TABLE #FoundTable

blog comments powered by Disqus