Way to search string in all tables in database

 

Some time we get requirement to find a particulare sting across tables in a data base.
One of my friend was trapped in same situation this the first solution we found on net


Search string in all tables-Ist Solution

but not tried yet we'll continue work on this and let you know once we get something new.

Here is a way to search string in all tables in database this is one of the solution tried by my friend Rohit Rao and it is working solution all credit goes to him.

DECLARE @SearchStr NVARCHAR(100) 

SET @SearchStr = 'F500P' 

CREATE TABLE #results 

  ( 

     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', 

                                                         'int', 'decimal' ) 

                                      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 columnname, 

       columnvalue 

FROM   #results 

DROP TABLE #results 

Happy Living...

Happy Concepts...

Happy Programming...