The Voice of the DBA

Auto-Deleting Data

Yesterday's Question (by Steve Jones):

I'm looking to determine some information about a file in one of my filegroups. I decide to the the FILEPROPERTY() function. Which of these are valid properties to query? (choose 3)

Answer: IsLogFile IsPrimaryFile SpaceUsed

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;

More »