Monday, May 20, 2013

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...