/*
*****************************************************************************************
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
Posts
