How to search all tables for a data?

All Replies

• Wednesday, May 23, 2012 7:54 PM

  Moderator

   

   
  

  2

  Take a look at this blog post

  How to search a value in all columns in all tables

  Alternatively, get a free SQL Search from Red-Gate or SSMS Boost from www.ssmsboost.com

  ---

  For every expert, there is an equal and opposite expert. - Becker's Law
  
  
  My blog

  
  

  • Edited by Naomi NMicrosoft Community Contributor, Moderator Wednesday, May 23, 2012 7:55 PM
  • Marked As Answer by Iric WenModerator Wednesday, May 30, 2012 1:53 PM
  •  
  • Reply
  • Quote

   
• Thursday, May 24, 2012 4:18 AM

   

   
  

  1

  Hello,

  I think this script can help you

  http://gallery.technet.microsoft.com/scriptcenter/c0c57332-8624-48c0-b4c3-5b31fe641c58

  Regards
  satheesh
  

  • Proposed As Answer by Iric WenModerator Friday, May 25, 2012 8:28 AM
  • Marked As Answer by Iric WenModerator Wednesday, May 30, 2012 1:53 PM
  •  
  • Reply
  • Quote

   
• Thursday, May 24, 2012 11:32 AM

   

   
  

  0

  Hi,

  You can create a procedure as below:

  ALTER PROC SearchAllTables
  (
      @SearchStr nvarchar(100)
  )
  AS
  BEGIN
  
  DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))
  SET NOCOUNT ON
  
  DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
  SET  @TableName = ''
  SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
  
  WHILE @TableName IS NOT NULL
  BEGIN
      SET @ColumnName = ''
      SET @TableName = 
      (
          SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
          FROM    INFORMATION_SCHEMA.TABLES
          WHERE       TABLE_TYPE = 'BASE TABLE'
              AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
              AND OBJECTPROPERTY(
                      OBJECT_ID(
                          QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                           ), 'IsMSShipped'
                             ) = 0
      )
  
      WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
      BEGIN
          SET @ColumnName =
          (
              SELECT MIN(QUOTENAME(COLUMN_NAME))
              FROM    INFORMATION_SCHEMA.COLUMNS
              WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                  AND TABLE_NAME  = PARSENAME(@TableName, 1)
                  AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                  AND QUOTENAME(COLUMN_NAME) > @ColumnName
          )
  
          IF @ColumnName IS NOT NULL
          BEGIN
              INSERT INTO @Results
              EXEC
              (
                  'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                  FROM ' + @TableName + ' (NOLOCK) ' +
                  ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
              )
          END
      END 
  END
  
  SELECT distinct(ColumnName), ColumnValue FROM @Results
  --select Count(*) FROM @Results
  END

  Then Execute the Procedure 

  EXEC SearchAllTables 'Word'

  It'll return all the Tables having the the word 'Word'.

  • Reply
  • Quote