/*
Name: SearchForData.SQL V1.0
Purpose: This SQL script searches for data in a database. The search can be configured to use different data types, exact match and case sensitivity.
How to Use: Before running this script, first change the @DataToFind value to what is to be searched for. Other configuration settings can also be made
in the "DEFINE SEARCH" section.
*/
-- PLEASE DEFINE SEARCH CRITERIA BEFORE RUNNING THIS SCRIPT.
USE Symantec_CMDB -- Change "Symantec_CMDB" to the database name to use if it is different.
DECLARE @DataToFind NVARCHAR(MAX) = 'value to search for' -- Change the value of @DataToFind to be what is to be searched for.
DECLARE @DataType NVARCHAR(6) = 'Text' -- Change the value of @DataType to be Text (default), Number, Guid, Date or All.
DECLARE @ExactMatch BIT = 0 -- Change the value of @ExactMatch to be 0 or 1. 0 equals a wildcard match (default), 1 equals an exact match.
DECLARE @CaseSensitive BIT = 0 -- Change the value of @CaseSentitive to be 0 or 1. 0 equals not case sentitive (default), 1 equals case sensitive.
--
DECLARE @Schema TABLE (RowID INT IDENTITY(1,1), STableName NVARCHAR(MAX), SColumnName NVARCHAR(MAX), SDataType NVARCHAR(MAX), DataSize NVARCHAR(100))
DECLARE @Found TABLE(FTableName NVARCHAR(MAX) DEFAULT '', FColumnName NVARCHAR(MAX) DEFAULT '', FDataType NVARCHAR(MAX), Match NVARCHAR(MAX))
DECLARE @LoopNo INT, @TotalRows INT, @SQL NVARCHAR(MAX), @ParamDef NVARCHAR(MAX), @DataExists BIT, @TableFound NVARCHAR(MAX), @ColumnFound NVARCHAR(MAX),
@DataTypeFound NVARCHAR(MAX), @DataTypeSize NVARCHAR(MAX), @DataTypesToUse1 NVARCHAR(16), @DataTypesToUse2 NVARCHAR(16), @DataTypesToUse3 NVARCHAR(16),
@DataTypesToUse4 NVARCHAR(16), @DataTypesToUse5 NVARCHAR(16), @DataTypesToUse6 NVARCHAR(16), @DataTypesToUse7 NVARCHAR(16), @DataTypesToUse8 NVARCHAR(16),
@DataTypesToUse9 NVARCHAR(16), @DataTypesToUse10 NVARCHAR(16), @DataTypesToUse11 NVARCHAR(16), @DataTypesToUse12 NVARCHAR(16), @DataTypesToUse13 NVARCHAR(16),
@DataTypesToUse14 NVARCHAR(16), @DataTypesToUse15 NVARCHAR(16), @DataTypesToUse16 NVARCHAR(16), @DataTypesToUse17 NVARCHAR(16), @DataTypesToUse18 NVARCHAR(16),
@DataTypesToUse19 NVARCHAR(16), @DataTypesToUse20 NVARCHAR(16)
--Note: This script will only search the defined data types as specified by the @DataType variable. For example, if String is the data type, numeric
--data types such as INT will not be searched, however, if the number being searched for exists in an NVARCHAR, the match will be found.
IF @DataType = 'Text'
BEGIN
SET @DataTypesToUse1 = 'NVARCHAR'
SET @DataTypesToUse2 = 'VARCHAR'
SET @DataTypesToUse3 = 'NTEXT'
SET @DataTypesToUse4 = 'TEXT'
SET @DataTypesToUse5 = 'NCHAR'
SET @DataTypesToUse6 = 'CHAR'
SET @DataTypesToUse7 = ''
SET @DataTypesToUse8 = ''
SET @DataTypesToUse9 = ''
SET @DataTypesToUse10 = ''
END
IF @DataType = 'Guid'
BEGIN
SET @DataTypesToUse1 = 'UNIQUEIDENTIFIER'
SET @DataTypesToUse2 = 'NVARCHAR'
SET @DataTypesToUse3 = ''
SET @DataTypesToUse4 = ''
SET @DataTypesToUse5 = ''
SET @DataTypesToUse6 = ''
SET @DataTypesToUse7 = ''
SET @DataTypesToUse8 = ''
SET @DataTypesToUse9 = ''
SET @DataTypesToUse10 = ''
END
IF @DataType = 'Number'
BEGIN
SET @DataTypesToUse1 = 'INT'
SET @DataTypesToUse2 = 'BIGINT'
SET @DataTypesToUse3 = 'SMALLINT'
SET @DataTypesToUse4 = 'TINYINT'
SET @DataTypesToUse5 = 'FLOAT'
SET @DataTypesToUse6 = 'REAL'
SET @DataTypesToUse7 = 'DECIMAL'
SET @DataTypesToUse8 = 'MONEY'
SET @DataTypesToUse9 = 'SMALLMONEY'
SET @DataTypesToUse10 = 'BIT'
END
-- Note: DateTime values are stored as (example) 2011-12-06 07:45:09.257, however, appear as Dec 6 2011 7:45AM.
-- To search for "2011-12-06", specify "Dec 6 2011". Also note that for single digit days, there is an extra space
-- after the month, as the above example shows.
IF @DataType = 'Date'
BEGIN
SET @ExactMatch = 0
SET @DataTypesToUse1 = 'DATETIME'
SET @DataTypesToUse2 = 'SMALLDATETIME'
SET @DataTypesToUse3 = 'NVARCHAR'
SET @DataTypesToUse4 = ''
SET @DataTypesToUse5 = ''
SET @DataTypesToUse6 = ''
SET @DataTypesToUse7 = ''
SET @DataTypesToUse8 = ''
SET @DataTypesToUse9 = ''
SET @DataTypesToUse10 = ''
END
IF @DataType = 'All'
BEGIN
SET @ExactMatch = 0
SET @DataTypesToUse1 = 'NVARCHAR'
SET @DataTypesToUse2 = 'VARCHAR'
SET @DataTypesToUse3 = 'NTEXT'
SET @DataTypesToUse4 = 'TEXT'
SET @DataTypesToUse5 = 'NCHAR'
SET @DataTypesToUse6 = 'CHAR'
SET @DataTypesToUse7 = 'UNIQUEIDENTIFIER'
SET @DataTypesToUse8 = 'INT'
SET @DataTypesToUse9 = 'BIGINT'
SET @DataTypesToUse10 = 'SMALLINT'
SET @DataTypesToUse11 = 'TINYINT'
SET @DataTypesToUse12 = 'FLOAT'
SET @DataTypesToUse13 = 'REAL'
SET @DataTypesToUse14 = 'DECIMAL'
SET @DataTypesToUse15 = 'MONEY'
SET @DataTypesToUse16 = 'SMALLMONEY'
SET @DataTypesToUse17 = 'BIT'
SET @DataTypesToUse19 = 'DATETIME'
SET @DataTypesToUse20 = 'SMALLDATETIME'
END
INSERT INTO @Schema(STableName, SColumnName, SDataType, DataSize)
SELECT T.TABLE_NAME, C.COLUMN_NAME, C.DATA_TYPE, C.CHARACTER_MAXIMUM_LENGTH
FROM Information_Schema.Tables AS T
JOIN Information_Schema.Columns AS C
ON T.TABLE_NAME = C.TABLE_NAME
WHERE T.TABLE_TYPE = 'BASE TABLE'
AND (DATA_TYPE = @DataTypesToUse1 OR DATA_TYPE = @DataTypesToUse2 OR DATA_TYPE = @DataTypesToUse3 OR DATA_TYPE = @DataTypesToUse4
OR DATA_TYPE = @DataTypesToUse5 OR DATA_TYPE = @DataTypesToUse6 OR DATA_TYPE = @DataTypesToUse7 OR DATA_TYPE = @DataTypesToUse8
OR DATA_TYPE = @DataTypesToUse9 OR DATA_TYPE = @DataTypesToUse10 OR DATA_TYPE = @DataTypesToUse11 OR DATA_TYPE = @DataTypesToUse12
OR DATA_TYPE = @DataTypesToUse13 OR DATA_TYPE = @DataTypesToUse14 OR DATA_TYPE = @DataTypesToUse15 OR DATA_TYPE = @DataTypesToUse16
OR DATA_TYPE = @DataTypesToUse17 OR DATA_TYPE = @DataTypesToUse18 OR DATA_TYPE = @DataTypesToUse19 OR DATA_TYPE = @DataTypesToUse20)
ORDER BY T.TABLE_NAME, C.COLUMN_NAME
SELECT @LoopNo = 1, @TotalRows = MAX(RowID)
FROM @Schema
WHILE @LoopNo <= @TotalRows
BEGIN
IF @ExactMatch = 1
SET @SQL = 'IF EXISTS(SELECT * FROM ReplaceTableName WHERE CONVERT(NVARCHAR(MAX), [ReplaceColumnName]) = ''' +
@DataToFind + ''') SET @DataExists = 1 ELSE SET @DataExists = 0'
ELSE
SET @SQL = 'IF EXISTS(SELECT * FROM ReplaceTableName WHERE CONVERT(NVARCHAR(MAX), [ReplaceColumnName]) LIKE ''%' +
@DataToFind + '%'') SET @DataExists = 1 ELSE SET @DataExists = 0'
SET @ParamDef = '@DataExists Bit OUTPUT'
SELECT @SQL = REPLACE(REPLACE(@SQL, 'ReplaceTableName', QUOTENAME(STableName)), 'ReplaceColumnName', SColumnName)
FROM @Schema
WHERE RowId = @LoopNo
EXEC SP_EXECUTESQL @SQL, @ParamDef, @DataExists = @DataExists OUTPUT
IF @DataExists = 1
BEGIN
SELECT @TableFound = STableName FROM @Schema WHERE RowID = @LoopNo
SELECT @ColumnFound = SColumnName FROM @Schema WHERE RowID = @LoopNo
SELECT @DataTypeFound = SDataType FROM @Schema WHERE RowID = @LoopNo
SELECT @DataTypeSize = DataSize
FROM @Schema
WHERE RowID = @LoopNo
IF @DataTypeSize <> ''
SET @DataTypeFound = @DataTypeFound + '(' + @DataTypeSize + ')'
SET @SQL = 'SELECT [' + @ColumnFound + '] FROM [' + @TableFound + '] WHERE CONVERT(NVARCHAR(MAX), [' + @ColumnFound + ']) '
IF @CaseSensitive = 1
SET @SQL = + @SQL + 'COLLATE Latin1_General_CS_AS '
IF @ExactMatch = 0
SET @SQL = @SQL + 'LIKE ''%' + @DataToFind + '%'''
ELSE
SET @SQL = @SQL + '= ''' + @DataToFind + ''''
INSERT INTO @Found(Match)
EXEC SP_EXECUTESQL @SQL
UPDATE @Found
SET FTableName = @TableFound, FColumnName = @ColumnFound, FDataType = @DataTypeFound
WHERE FTableName = ''
END
SET @LoopNo = @LoopNo + 1
END
IF @ExactMatch = 0
SELECT FTableName AS 'Table', FColumnName AS 'Column', FDataType AS 'Data Type', Match -- Note: If the Data Type is not desired to be seen, remove this here.
FROM @Found
ORDER BY FTableName, FColumnName, Match
ELSE
SELECT FTableName AS 'Table', FColumnName AS 'Column', FDataType AS 'Data Type' -- Note: If the Data Type is not desired to be seen, remove this here.
FROM @Found
ORDER BY FTableName, FColumnName
No comments:
Post a Comment