Search Value in Database

/*

 

*****************************************************************************************

Function: This stored procedure is used to search punctuation character or text content by scanning all table columns avalilable

in specified MSSQL Server Database.

*****************************************************************************************

This stored procedure is searched for following Data Type only

1) Text

2) Varchar

3) Nvarchar

This stored procedure has  following optional parameters.

1) @IsBlackBox

      This parameter defined whether stored procedure is running on Black Box or any other MS SQL Server Database.

      -     Data type of this parameter is INT (Integer).

      -     If stored procedure needs to run  on Black Box then set this parameter value needs to set to 1 (numeric one)

      -     If stored procedure needs to run on other MS SQL database server Parameter value needs to set to 0 (numeric zero)

      -     Default parameter value is 1 (numeric one)

     

2) @SearchCharacters

      This parameter defined for which punctuation / special character needs to be searched.

      -     Data type of this parameter is NVARCHAR (supports Unicode)

      -     Pass one or more punctuation /special  characters need to be searched. i.e. ‘;’~’ (Please do not add any delimiters)

      -     Default parameter value is ‘^0-9A-Za-z’  (Retrieve list of content  which has data value except alphanumeric)

     

3)  @SearchString

      This parameter defined for which string value needs to be searched.

      -     Data type of this parameter is NVARCHAR (supports Unicode)

      -     Pass one string needs to be searched. i.e. ‘John’

      -     Default parameter value is ‘ ’ [blank string]  (Retrieve all the content)

      *     Note: If  @SearchCharacters parameter passed with a value then this parameter value will be ignored.

                  This parameter works when @SearchCharacters value is blank.

                 

4) @Column

      This parameter defined for which column needs to be searched.

      -     Data type of this parameter is VARCHAR

      -     Pass column name need to be searched. i.e.  ‘pname’

      -     Default parameter value is ‘ ’ [blank string]  (Retrieve all the columns )

4) @Table

      This parameter defined for which table needs to be searched.

      -     Data type of this parameter is VARCHAR

      -     Pass table name need to be searched. i.e.  ‘xpat’

      -     Default parameter value is ‘ ’ [blank string]  (Retrieve all the tables )

*****************************************************************************************

Execution Methods

1) EXECUTE BlackBox_Search

            -Search with parameters default value

           

2) EXECUTE BlackBox_Search 1,';','','',''

            -search on black box schema for ';' character in all columns of all tables

                 

3)EXECUTE BlackBox_Search 1,'','john','',''

            -search on black box schema for value contains string 'john' in all columns of all tables

3)EXECUTE BlackBox_Search 1,'','john','plname','xpat'

            -search on black box schema for value contains string 'john' in plname column of xpat table

*****************************************************************************************                 

*/

CREATE PROCEDURE BlackBox_Search

(

      @IsBlackBox INT = 1, -- 1 for true , 0 for false

      @SearchCharacters NVARCHAR(200) = N'', -- Pass the Character/s needs to be searched

      @SearchString NVARCHAR(2000) = N'', -- Pass the string needs to be searched

      @Column VARCHAR(200) = '', -- Pass the column name

      @Table VARCHAR(200) = '' -- Pass the table name

)

AS

BEGIN

SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

/*****************************************************************************************/

-- Table Declaration

/*****************************************************************************************/

CREATE TABLE #TMP

(

      ID NUMERIC(18,0) IDENTITY,

      TableName VARCHAR(1000),

      ColumnName VARCHAR(1000),

      ColumnType VARCHAR(1000)

)

CREATE TABLE #Results

(

      ID NUMERIC(18,0) IDENTITY,

      IID NUMERIC(18,0) DEFAULT NULL,

      TableName NVARCHAR(2000) DEFAULT NULL,

      ColumnName NVARCHAR(2000) DEFAULT NULL,

      --ColumnType NVARCHAR(2000),

      ColumnValue NVARCHAR(2000)

)

/*****************************************************************************************/

-- Variable Declaration

/*****************************************************************************************/

DECLARE @Query NVARCHAR(2000)

SET @Query = N''

DECLARE @RowCount INT

SET @RowCount = 0

DECLARE @CurrentTableName NVARCHAR(1000)

DECLARE @CurrentColumnName NVARCHAR(1000)

/*****************************************************************************************/

-- Parameter Filteration

/*****************************************************************************************/

SET @SearchCharacters = REPLACE(@SearchCharacters,'''','''''')

SET @SearchCharacters = REPLACE(@SearchCharacters,'%','')

SET @SearchString = REPLACE(@SearchString,'''','''''')

SET @SearchString = REPLACE(@SearchString,'%','')

/*****************************************************************************************/

-- SET SEARCH CHARCHTER CONDITION

/*****************************************************************************************/

DECLARE @WhereCondition NVARCHAR(200)

--SET @WhereCondition = N' LIKE N''%[^0-9A-Za-z .,;:]%'' collate Latin1_General_BIN'

IF (@SearchCharacters is null or rtrim(ltrim(@SearchCharacters)) = '')

      BEGIN

            SET @WhereCondition = N' LIKE N''%[^0-9A-Za-z]%'' collate Latin1_General_BIN'

      END

ELSE

      BEGIN

            SET @WhereCondition = N' LIKE N''%['+@SearchCharacters+']%'' collate Latin1_General_BIN'

      END

/*****************************************************************************************/

-- SET SEARCH STRING CONDITION

/*****************************************************************************************/

IF (@SearchCharacters IS NULL OR  rtrim(ltrim(@SearchCharacters)) = '')

BEGIN

      IF ((@SearchString IS NOT NULL AND  rtrim(ltrim(@SearchString)) <> ''))

      BEGIN

            SET @WhereCondition = N' LIKE N''%'+@SearchString+'%'''

      END

END

/*****************************************************************************************/

-- Fetch Data with Table name and Column name (Applied Filter as per the parameter passed)

/*****************************************************************************************/

INSERT INTO #TMP

 SELECT t.name AS TableName,

c.name AS ColumnName

,ty.name as ColumnType

FROM sys.tables AS t

INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

AND T.name LIKE ( CASE WHEN @Table is NULL OR @Table = '' THEN '%' ELSE @table END)

AND C.name LIKE ( CASE WHEN @Column is NULL OR @Column = '' THEN '%' ELSE @Column+'' END)

INNER JOIN SYS.types AS ty ON C.user_type_id = TY.user_type_id

AND Ty.name in ('text','varchar','nvarchar')

ORDER BY t.name;

/*****************************************************************************************/

-- Query Building and Execution of Query

/*****************************************************************************************/

SELECT  @RowCount = COUNT(1)  FROM #TMP

IF (@RowCount > 0)

BEGIN

     

      WHILE (@RowCount > 0)

      BEGIN

                  SET @CurrentTableName = ''

                  SET @CurrentColumnName = ''

                  SET @Query = ''

                 

                  SELECT @CurrentTableName = TableName , @CurrentColumnName = ColumnName   FROM #TMP WHERE ID = @RowCount

                 

                  -- If search for Black Box

                  IF (@IsBlackBox = 1)

                        BEGIN

                                    IF (@CurrentTableName not like 'xCodeSystemMaster')

                                    BEGIN

                                           SET @Query = N'INSERT INTO  #Results

                                                                  SELECT  IID

                                                                  , '''+@CurrentTableName+''' AS TableName

                                                                  , '''+@CurrentColumnName+''' AS ColumnName

                                                                  ,['+@CurrentColumnName+'] as ColumnValue FROM '+@CurrentTableName+' WHERE ['+@CurrentColumnName+']'+@WhereCondition

                                     

                                     END

                                     

                                     ELSE

                                           BEGIN

                                                 SET @Query = N'INSERT INTO  #Results

                                                                  SELECT  NULL AS IID

                                                                  , '''+@CurrentTableName+''' AS TableName

                                                                  , '''+@CurrentColumnName+''' AS ColumnName

                                                                  ,['+@CurrentColumnName+'] as ColumnValue FROM '+@CurrentTableName+' WHERE ['+@CurrentColumnName+']'+@WhereCondition

                                           

                                           END

                       

                        END

                  ELSE

                        BEGIN

                       

                                     SET @Query = N'INSERT INTO  #Results

                                                                  SELECT  NULL AS IID

                                                                  , '''+@CurrentTableName+''' AS TableName

                                                                  , '''+@CurrentColumnName+''' AS ColumnName

                                                                  ,['+@CurrentColumnName+'] as ColumnValue FROM '+@CurrentTableName+' WHERE ['+@CurrentColumnName+']'+@WhereCondition

                       

                        END

                 

                 

                  -- Execute Build Query 

                   EXECUTE(@Query)

                 

                 

     

     

      SET @RowCount = @RowCount - 1

      END

END

/*****************************************************************************************/

-- Selection of Final Results

/*****************************************************************************************/

SELECT TableName,IID,ColumnName,ColumnValue as Value FROM #Results

/*****************************************************************************************/

-- Drop the Objects

/*****************************************************************************************/

DROP TABLE #TMP

DROP TABLE #Results

/*****************************************************************************************/

END