Searching from Entire Database

Searching from ENTIRE DATABASE is the common requirement for every dba/developer in organization.

My boss is always asking to me Suman Check this 958200000 MOBILE NUMBER or smnjha989@gmail.com  E-ID where is available and replace with this new number, he/she left the Position but still getting notification in mail or alert in mobile…  It was very time consuming task for me …

But Now this is easy for me for finding. Just running the below query and after few minute gives the table name-column name and value where it contains………….. I am using below TSQL just try………..

    

DECLARE @SearchingText nvarchar(256)

SET  @SearchingText = 'XXXXXXXXXXXXX'




BEGIN

      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('%' + @SearchingText + '%','''')




      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 ColumnName, ColumnValue FROM

 

#Results

END

Result:- 

@sumanjha