Updated Brute Force Search Tool
Name:
USP_SQLBruteForce
Compatibility:
Works with SQL Server 2012 and newer versions. This will not work in SQL Server 2008 because it makes use of concat, iif, and throw. These could be updated to use standard string concatenation, case statements, and raiseerror instead to make it work on older versions of SQL Server, however I will not be providing that here.
Description:
Searches a database for a value in any or all tables, supports number, date, and text searches along with fuzzy searches like begins with, ends with, contains, and now includes pattern matching
Returns:
Table of matches found in database with sql to pull up the data and a count of how many times it was found in a specific table and column
Notes:
I had previously published another version of this SP and decided it was time to update it a bit. First I added in pattern matching for text searches as I have found it is fairly useful if your wanting to for example find every column in the db that contains dates in text or phone numbers in text. This makes use of patindex for this so it will allow for anything patindex allows for. I added in the ability to get printed messages to the screen as its finding results. This allows you to see the results as they are found and can be very helpful if your searches are taking 20 minutes to complete to get a start on some of the results as its still processing. Errors are now by default suppressed but can also be turned on to see any columns that errored in the search including the details of the error. The next big improvement was adding in the ability to only search specific tables, or skip specific tables. So now this supports including or excluding tables from the search. Really handy if you only want to search a few tables, or if you want to skip the tables with a lot of binary data in them. It will not allow for both at the same time however as that would be rather silly to include tables in one list then exclude them in the next. Added tons of comments into the script to allow for users to easily see whats going on in it and modify to their liking.
Why is this useful?
I spend most of my time working on transforming unknown data into our client CRM databases and over the years I have found that a brute force script can help figure out the data structure of the source database. There are many times where I only get an example value to match on and verify I got the data transformed correctly. This script allows me to take a random value and find it in the source database, then once found I can work on the transforms over to my system. It also helps locate lookup tables, find every reference to some random id im looking at, and get an overall idea how data is structured. For me I tend to use this almost daily for what I do and is why I decided to share it a few years ago.
Usage:
The following examples are also included in the script as well
--STRING SEARCHES------------------------------------------------
--exact match
--returns all table columns where there is an exact match of the search value
EXEC USP_SQLBruteForce
@SearchValue = 'test',
@SearchType = 'text';
--contains
--returns all table columns where the search value is contained in the data
EXEC USP_SQLBruteForce
@SearchValue = 'test',
@SearchType = 'text',
@IsFuzzy = 1,
@FuzzyType = 'contains';
--begins with
--returns all table columns where the data begins with the search value
EXEC USP_SQLBruteForce
@SearchValue = 'test',
@SearchType = 'text',
@IsFuzzy = 1,
@FuzzyType = 'begins';
--ends with
--returns all table columns where the data ends with the search value
EXEC USP_SQLBruteForce
@SearchValue = 'test',
@SearchType = 'text',
@IsFuzzy = 1,
@FuzzyType = 'ends';
--pattern
--returns all table columns where the pattern supplied had matches in data
EXEC USP_SQLBruteForce
@SearchValue = '[0-9][0-9][0-9][-][0-9][0-9][0-9][0-9]',
@SearchType = 'text',
@IsFuzzy = 1,
@FuzzyType = 'advanced';
--DATE SEARCH----------------------------------------------------
--returns all table columns where the date was found
EXEC USP_SQLBruteForce
@SearchValue = '01-01-1980',
@SearchType = 'date';
--NUMBER SEARCH--------------------------------------------------
--returns all table columns where the number was found
EXEC USP_SQLBruteForce
@SearchValue = '1234567890',
@SearchType = 'number';
--ADVANCED USAGE
--exact match of text in included tables only
EXEC USP_SQLBruteForce
@SearchValue = 'test',
@SearchType = 'text',
@IncludeTables = 1,
@IncludeTableList = 'table1,table2,table3';
--exact match of text excluding some tables from search
EXEC USP_SQLBruteForce
@SearchValue = 'test',
@SearchType = 'text',
@ExcludeTables = 1,
@ExcludeTableList = 'table1,table2,table3';
--search with print on
EXEC USP_SQLBruteForce
@SearchValue = 'test',
@SearchType = 'text',
@PrintResults = 1;
--search with errors and print on
EXEC USP_SQLBruteForce
@SearchValue = 'test',
@SearchType = 'text',
@PrintResults = 1,
@ErrorDsp = 1;
IF OBJECT_ID ( 'USP_SQLBruteForce', 'P' ) IS NOT NULL
DROP PROCEDURE [dbo].USP_SQLBruteForce;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*********************************************************************************************
SQLBruteForce v3 (2018-04-20)
(C) 2018, John Imel
DESCRIPTION:
searches a database for a value in any or all tables, supports number, datetime, and text searches along with
fuzzy searches like begins with, ends with, contains, and advanced which allows for pattern searches
RETURN:
table of matches found in database with sql to pull up the data and a count of how many times it was found in a specific table and column
LICENSE:
SQLBruteForce is free to download and use for personal, educational, and internal
corporate purposes, provided that this header is preserved. Redistribution is allowed as long as this
header is preserved and is provided for free with no additional costs, any other redistribution or sale
of SQLBruteForce, in whole or in part, is prohibited without the author's express
written consent.
*********************************************************************************************/
CREATE PROCEDURE USP_SQLBruteForce
--REQUIRED PARAMETERS-------------------------------
--SEARCH TERM
@SearchValue varchar(255),
--SEARCH TYPE supports the following: number, date, text
@SearchType varchar(8),
--OPTIONAL PARAMETERS----------------------------------
--is this a fuzzy search or exact match? 1 = fuzzy 0 = exact match only works for text searches
@IsFuzzy BIT = 0,
--type of fuzzy search to perform, Supports the following begins, ends, contains, advanced
@FuzzyType varchar(8) = '',
--Show or Hide errors encountered, 1 = show, 0 = hide
@ErrorDsp BIT = 0,
--Print results to screen, uses nowait option of raise error to print results as they are found
@PrintResults BIT = 0,
--limit search to specific tables 1 = limit, 0 = dont limit
@IncludeTables BIT = 0,
--CSV list of tables you want to search through
@IncludeTableList varchar(4000) = '',
--Exclude tables from search 1 = exclude, 0 = dont exclude
@ExcludeTables BIT = 0,
--CSV list of tables you want to exclude from search
@ExcludeTableList varchar(4000) = ''
as
BEGIN
--set nocount on to suppress messages back to client
SET NOCOUNT ON
--DECLARATIONS-------------------------------------------
DECLARE
@SearchStringLength int,
@ReturnSQLStatement nvarchar(4000),
@ReturnSQLStatementCount nvarchar(4000),
@ResultsCounter int,
@TableName varchar(200),
@ColumnName varchar(200),
@SchemaName varchar(200),
@ErrorText nvarchar(4000),
@ParmDefinition nvarchar(50),
@ExcludeListText varchar(4000),
@IncludeListText varchar(4000);
--table variables
declare @coltypes table(typename varchar(200) not null, typecategory varchar(200) not null primary key (typename, typecategory));
declare @collist table (tableid int identity primary key, schemaname varchar(200) not null, tablename varchar(200) not null, columnname varchar(200) not null, columnlength int, columntype varchar(200) unique clustered (schemaname,tablename, columnname));
declare @results table (resultid int identity primary key, resultsql nvarchar(4000), resultcntsql nvarchar(4000), schemaname varchar(200) not null, tablename varchar(200) not null, columnname varchar(200) not null);
--Temp table creation
if(OBJECT_ID('tempdb..#finalresults') is not null)
drop table #finalresults;
--used to hold the matches
create table #finalresults(resultid int identity primary key, resultsql varchar(4000), resultcnt int, schemaname varchar(200) not null, tablename varchar(200) not null, columnname varchar(200));
if(OBJECT_ID('tempdb..#errorresults') is not null)
drop table #errorresults;
--used to hold the errors
create table #errorresults(resultid int identity primary key, schemaname varchar(200) not null, tablename varchar(200) not null, columnname varchar(200), resultsql varchar(4000), errortxt nvarchar(4000));
--END DECLARATIONS---------------------------------------
--Initialize Variables and helper tables-----------------
SET @ParmDefinition = N'@cntvalOUT int OUTPUT';
SET @SearchStringLength = LEN(@SearchValue);
--helper table to reduce columns being searched through
--you can always add to this table or remove to customize which columns should be searched
insert into @coltypes(typename, typecategory)
values
('text','text'),
('date','date'),
('datetime2','date'),
('datetimeoffset','date'),
('tinyint','number'),
('smallint','number'),
('int','number'),
('smalldatetime','date'),
('real','number'),
('money','number'),
('datetime','date'),
('float','number'),
('ntext','text'),
('decimal','number'),
('smallmoney','number'),
('bigint','number'),
('varchar','text'),
('char','text'),
('nvarchar','text'),
('nchar','text'),
('name','text'),
('uniqueidentifier','text');
--END Initialize Variables-------------------------------
--START CHECKS-------------------------------------------
--do we have a search value?
IF(LEN(@SearchValue) = 0)
BEGIN
THROW 51000,'No search value set!! Cant search for nothing silly:) so please populate @SearchValue and retry',1;
END
--make sure searchtype chosen is supported
IF(LEN(@SearchType) = 0 OR @SearchType NOT IN ('text','date','number'))
BEGIN
THROW 51000,'Incorrect SearchType!! @SearchType only supports values of text, date, or number.',1;
END
--make sure fuzzytype chosen is supported
IF(@IsFuzzy = 1 and @FuzzyType NOT IN ('begins','ends','contains','advanced'))
BEGIN
THROW 51000,'Incorrect FuzzyType!! @FuzzyType only supports values of begins, ends, contains, advanced.',1;
END
--make sure @IncludeTableList has a value if @IncludeTables is set to 1
IF(@IncludeTables = 1 and len(@IncludeTableList) = 0)
BEGIN
THROW 51000,'Table Include List Empty!! If @IncludeTables set to 1 then you must have AT least one table name in @IncludeTableList.',1;
END
--make sure @ExcludeTableList has a value if @ExcludeTables is set to 1
IF(@ExcludeTables = 1 and len(@ExcludeTableList) = 0)
BEGIN
THROW 51000,'Table Exclude List Empty!! If @ExcludeTables set to 1 then you must have AT least one table name in @ExcludeTableList.',1;
END
--make sure @ExcludeTables and @IncludeTables are not both set to 1
IF(@ExcludeTables = 1 and @IncludeTables = 1)
BEGIN
THROW 51000,'Detected inclusion and exclusions both set to true!! You cannot set both @ExcludeTables = 1 and @IncludeTables = 1 only one can be used at a Time.',1;
END
--END CHECKS---------------------------------------------
--Build up a list of tables and columns tht will be searched through
insert into @collist(schemaname,tablename,columnname,columnlength,columntype)
select distinct ss.name,t.name, c.name, c.max_length, tt.name
from sys.tables t
inner join sys.columns c on t.object_id = c.object_id
inner join sys.types tt on c.user_type_id = tt.user_type_id
inner join sys.schemas ss on t.schema_id = ss.schema_id
inner join @coltypes ct on tt.name = ct.typename and ct.typecategory = @SearchType
where 1 = 1
AND c.is_computed = 0
--filter these results by include and exclude lists
IF @IncludeTables = 1
BEGIN
--convert list to have pipes surrounding values
SET @IncludeListText = CONCAT( '|', REPLACE(REPLACE(REPLACE(@IncludeTableList,' ,',','),', ',','),',','|,|'), '|')
--remove records from results
DELETE FROM @collist WHERE charindex('|' + tablename + '|', @IncludeListText, 1) = 0
END
IF @ExcludeTables = 1
BEGIN
--convert list to have pipes surrounding values
SET @ExcludeListText = CONCAT( '|', REPLACE(REPLACE(REPLACE(@ExcludeTableList,' ,',','),', ',','),',','|,|'), '|')
--remove records from results
DELETE FROM @collist WHERE charindex('|' + tablename + '|', @ExcludeListText, 1) > 0
END
--now with the table and column list lets start building out the sql needed to perform the search and place into a table variable
if @SearchType = 'text'
BEGIN
--BEGIN TEXT SEARCH QUERY BUILD----------------------
--check isfuzzy
if(@IsFuzzy = 0)
BEGIN
--exact match
insert into @results(resultsql,resultcntsql,schemaname,tablename,columnname)
select
CONCAT(
'select ',
'[' + columnname + '], *',
' FROM ',
'[' + schemaname + '].',
'[' + tablename + ']',
' WHERE ',
'[' + columnname + ']',
iif(c.columntype in ('text','ntext','uniqueidentifier'),' LIKE ',' = '),
''''+@SearchValue+''''
) AS resultsql,
CONCAT(
'select ',
'@cntvalOUT = COUNT(1)',
' FROM ',
'[' + schemaname + '].',
'[' + tablename + ']',
' WHERE ',
'[' + columnname + ']',
iif(c.columntype in ('text','ntext','uniqueidentifier'),' LIKE ',' = '),
''''+@SearchValue+''''
) AS resultcntsql,
c.schemaname,
c.tablename,
c.columnname
from @collist c
where 1 = 1
and (
c.columntype in ('text','ntext','uniqueidentifier')
OR
c.columnlength = -1
OR
--added to remove columns shorter than the search value
c.columnlength >= @SearchStringLength
)
--end exact match
END
ELSE
BEGIN
--fuzzy match
IF(@FuzzyType = 'begins')
BEGIN
--begin begins with
insert into @results(resultsql,resultcntsql,schemaname,tablename,columnname)
select concat(
'select ',
'[' + columnname + '], *',
' FROM ',
'[' + schemaname + '].',
'[' + tablename + ']',
' WHERE ',
'[' + columnname + ']',
' LIKE ',
''''+@SearchValue+'%'''
),concat(
'select ',
'@cntvalOUT = COUNT(1)',
' FROM ',
'[' + schemaname + '].',
'[' + tablename + ']',
' WHERE ',
'[' + columnname + ']',
' LIKE ',
''''+@SearchValue+'%'''
),c.schemaname,c.tablename,c.columnname
from @collist c
where 1 = 1
and (
c.columntype in ('text','ntext','uniqueidentifier')
or
c.columnlength = -1
or
c.columnlength >= @SearchStringLength
)
--end begins with
END
ELSE IF(@FuzzyType = 'ends')
BEGIN
--begin ends with
insert into @results(resultsql,resultcntsql,schemaname,tablename,columnname)
select concat(
'select ',
'[' + columnname + '], *',
' FROM ',
'[' + schemaname + '].',
'[' + tablename + ']',
' WHERE ',
'[' + columnname + ']',
' LIKE ',
''''+@SearchValue+'%'''
),concat(
'select ',
'@cntvalOUT = COUNT(1)',
' FROM ',
'[' + schemaname + '].',
'[' + tablename + ']',
' WHERE ',
'[' + columnname + ']',
' LIKE ',
'''%'+@SearchValue+''''
),c.schemaname,c.tablename,c.columnname
from @collist c
where 1 = 1
and (
c.columntype in ('text','ntext','uniqueidentifier')
or
c.columnlength = -1
or
c.columnlength >= @SearchStringLength
)
--end ends with
END
ELSE IF(@FuzzyType = 'contains')
BEGIN
--begin contains
insert into @results(resultsql,resultcntsql,schemaname,tablename,columnname)
select concat(
'select ',
'[' + columnname + '], *',
' FROM ',
'[' + schemaname + '].',
'[' + tablename + ']',
' WHERE ',
'charindex('''+@SearchValue+''',[' + columnname + '],1) > 0'
),concat(
'select ',
'@cntvalOUT = COUNT(1)',
' FROM ',
'[' + schemaname + '].',
'[' + tablename + ']',
' WHERE ',
'charindex('''+@SearchValue+''',[' + columnname + '],1) > 0'
),c.schemaname,c.tablename,c.columnname
from @collist c
where 1 = 1
and (
c.columntype in ('text','ntext','uniqueidentifier')
or
c.columnlength = -1
or
c.columnlength >= @SearchStringLength
)
--end contains
END
ELSE IF(@FuzzyType = 'advanced')
BEGIN
--begin advanced
insert into @results(resultsql,resultcntsql,schemaname,tablename,columnname)
select concat(
'select ',
'[' + columnname + '], *',
' FROM ',
'[' + schemaname + '].',
'[' + tablename + ']',
' WHERE ',
'patindex('''+@SearchValue+''',[' + columnname + ']) > 0'
),concat(
'select ',
'@cntvalOUT = COUNT(1)',
' FROM ',
'[' + schemaname + '].',
'[' + tablename + ']',
' WHERE ',
'patindex('''+@SearchValue+''',[' + columnname + ']) > 0'
),c.schemaname,c.tablename,c.columnname
from @collist c
where 1 = 1
and (
c.columntype in ('text','ntext','uniqueidentifier')
or
c.columnlength = -1
)
--end advanced
END
END
--end check isfuzzy
--END TEXT SEARCH QUERY BUILD------------------------
END
ELSE IF @SearchType = 'date'
BEGIN
--BEGIN DATE SEARCH QUERY BUILD----------------------
insert into @results(resultsql,resultcntsql,schemaname,tablename,columnname)
select concat(
'select ',
'[' + columnname + '], *',
' FROM ',
'[' + schemaname + '].',
'[' + tablename + ']',
' WHERE ',
'try_cast([' + columnname + '] as date)',
' = ',
''''+@SearchValue+''''
),concat(
'select ',
'@cntvalOUT = COUNT(1)',
' FROM ',
'[' + schemaname + '].',
'[' + tablename + ']',
' WHERE ',
'try_cast([' + columnname + '] as date)',
' = ',
''''+@SearchValue+''''
),c.schemaname,c.tablename,c.columnname
from @collist c
where 1 = 1
--END DATE SEARCH QUERY BUILD------------------------
END
ELSE IF @SearchType = 'number'
BEGIN
--BEGIN NUMBER SEARCH QUERY BUILD----------------------
insert into @results(resultsql,resultcntsql,schemaname,tablename,columnname)
select concat(
'select ',
'[' + columnname + '], *',
' FROM ',
'[' + schemaname + '].',
'[' + tablename + ']',
' WHERE ',
'[' + columnname + ']',
' = ',
@SearchValue
),concat(
'select ',
'@cntvalOUT = COUNT(1)',
' FROM ',
'[' + schemaname + '].',
'[' + tablename + ']',
' WHERE ',
'[' + columnname + ']',
' = ',
@SearchValue
),c.schemaname,c.tablename,c.columnname
from @collist c
where 1 = 1
--END NUMBER SEARCH QUERY BUILD------------------------
END
--now we have a temp table with queries ready to be looped through
--lets do a cursor and have it do the work
--declare the cursor
DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FORWARD_ONLY FOR
select resultsql,resultcntsql,schemaname,tablename,columnname from @results
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @ReturnSQLStatement, @ReturnSQLStatementCount, @SchemaName, @TableName, @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
--use try catch to catch any errors as this can and will error from time to time
BEGIN TRY
--execute the statement adn record teh count of results in the counter
EXEC sp_executesql @ReturnSQLStatementCount, @ParmDefinition, @cntvalOUT=@ResultsCounter OUTPUT;
IF(@ResultsCounter is null)
BEGIN
set @ResultsCounter = 0;
END
--insert results into temp results table
INSERT INTO #finalresults(resultsql,resultcnt,schemaname,tablename,columnname)
SELECT @ReturnSQLStatement,@ResultsCounter, @SchemaName, @TableName, @ColumnName
--if printresults is true then print it out using raiseerror with nowait
IF @PrintResults = 1 AND @ResultsCounter > 0
BEGIN
RAISERROR (@ReturnSQLStatement, 0, 1) WITH NOWAIT;
END
END TRY
BEGIN CATCH
--error caught and error display is set to true
IF(@ErrorDsp = 1)
BEGIN
SET @ErrorText = ( select CONCAT(
'Error Number: ',
ERROR_NUMBER(),
'Severity: ',
ERROR_SEVERITY(),
'Procedure: ',
ERROR_PROCEDURE(),
'Line: ',
ERROR_LINE(),
'Message: ',
ERROR_MESSAGE()
));
--insert error into error table
INSERT INTO #errorresults(schemaname,tablename,columnname,resultsql,errortxt)
SELECT @SchemaName,@TableName,@ColumnName,@ReturnSQLStatement,@ErrorText
END
ELSE
BEGIN
--error dispaly is set to false
IF @PrintResults = 1
BEGIN
--raise error with no wait if print results is set to true
SET @ErrorText = CONCAT('error occurred in [', @SchemaName, '].[', @TableName, '].[', @ColumnName, ']');
RAISERROR (@ErrorText, 0, 1) WITH NOWAIT;
END
ELSE
BEGIN
--simple print of error if print results set to false
PRINT CONCAT('error occurred in [', @SchemaName, '].[', @TableName, '].[', @ColumnName, ']');
END
END
END CATCH
FETCH NEXT FROM db_cursor INTO @ReturnSQLStatement, @ReturnSQLStatementCount, @SchemaName, @TableName, @ColumnName
END
CLOSE db_cursor
DEALLOCATE db_cursor
--END Cursor
--now processing is done lets return the results
select resultsql as ResultSQL,resultcnt as NumberOfMatches,schemaname,tablename,columnname
from #finalresults
where resultcnt > 0
order by resultcnt
--if errors are on then return those as well
if(@ErrorDsp = 1)
select * from #errorresults order by schemaname,tablename,columnname
--cleanup
BEGIN TRY
DROP TABLE #finalresults
DROP TABLE #errorresults
END TRY
BEGIN CATCH
PRINT 'temp tables already gone, skipping drop'
END CATCH
SET NOCOUNT OFF
END
GO
/*
--USAGE EXAMPLES--
--STRING SEARCHES------------------------------------------------
--exact match
--returns all table columns where there is an exact match of the search value
EXEC USP_SQLBruteForce
@SearchValue = 'test',
@SearchType = 'text';
--contains
--returns all table columns where the search value is contained in the data
EXEC USP_SQLBruteForce
@SearchValue = 'test',
@SearchType = 'text',
@IsFuzzy = 1,
@FuzzyType = 'contains';
--begins with
--returns all table columns where the data begins with the search value
EXEC USP_SQLBruteForce
@SearchValue = 'test',
@SearchType = 'text',
@IsFuzzy = 1,
@FuzzyType = 'begins';
--ends with
--returns all table columns where the data ends with the search value
EXEC USP_SQLBruteForce
@SearchValue = 'test',
@SearchType = 'text',
@IsFuzzy = 1,
@FuzzyType = 'ends';
--pattern
--returns all table columns where the pattern supplied had matches in data
EXEC USP_SQLBruteForce
@SearchValue = '[0-9][0-9][0-9][-][0-9][0-9][0-9][0-9]',
@SearchType = 'text',
@IsFuzzy = 1,
@FuzzyType = 'advanced';
--DATE SEARCH----------------------------------------------------
--returns all table columns where the date was found
EXEC USP_SQLBruteForce
@SearchValue = '01-01-1980',
@SearchType = 'date';
--NUMBER SEARCH--------------------------------------------------
--returns all table columns where the number was found
EXEC USP_SQLBruteForce
@SearchValue = '1234567890',
@SearchType = 'number';
--ADVANCED USAGE
--exact match of text in included tables only
EXEC USP_SQLBruteForce
@SearchValue = 'test',
@SearchType = 'text',
@IncludeTables = 1,
@IncludeTableList = 'table1,table2,table3';
--exact match of text excluding some tables from search
EXEC USP_SQLBruteForce
@SearchValue = 'test',
@SearchType = 'text',
@ExcludeTables = 1,
@ExcludeTableList = 'table1,table2,table3';
--search with print on
EXEC USP_SQLBruteForce
@SearchValue = 'test',
@SearchType = 'text',
@PrintResults = 1;
--search with errors and print on
EXEC USP_SQLBruteForce
@SearchValue = 'test',
@SearchType = 'text',
@PrintResults = 1,
@ErrorDsp = 1;
*/
