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 cought 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 = 'search string here'

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

 

No comments:

Post a Comment