--\
---) SearchNumberInDatabase script.
---) Author: Hans Michiels
---) Searches for one or more numeric values in all TINYINT, SMALLINT,
---) INT, FLOAT, DECIMAL and NUMERIC columns of a SQL Server database.
---) WARNING: Can potentially run very long, my advise is not
---) to run this script in production environments!
--/
/*
(c) Copyright 2016 - hansmichiels.com
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program. If not, see http://www.gnu.org/licenses/.
*/
USE [AdventureWorks2014]
GO
--\-----------------------------------------------------------------------------------
---) DECLARATIONS
--/-----------------------------------------------------------------------------------
DECLARE @schema_name SYSNAME
DECLARE @table_name SYSNAME
DECLARE @column_name SYSNAME
DECLARE @nsql NVARCHAR(4000)
DECLARE @param_def NVARCHAR(500)
DECLARE @search_value DECIMAL(38, 2)
DECLARE @search_values TABLE ([search_value] DECIMAL(38, 2))
DECLARE @debug_print NVARCHAR(500)
DECLARE @results_count INT = 0
DECLARE @max_difference DECIMAL(38, 2) -- Use to find values 'near by'
IF OBJECT_ID('tempdb..#search_results') IS NOT NULL BEGIN
DROP TABLE #search_results
END
CREATE TABLE #search_results
( [search_value] DECIMAL(38, 2)
, [schema_name] NVARCHAR(128)
, [table_name] NVARCHAR(128)
, [column_name] NVARCHAR(128)
, [the_value] DECIMAL(38, 2)
)
DECLARE columns_cursor CURSOR LOCAL STATIC FOR
SELECT sch.name AS [schema_name]
, tbl.name AS [table_name]
, col.name AS [column_name]
FROM sys.tables tbl
JOIN sys.schemas sch
ON sch.schema_id = tbl.schema_id
JOIN sys.columns col
ON col.object_id = tbl.object_id
-- TINYINT, SMALLINT, INT, INT
WHERE col.system_type_id IN (48, 52, 56, 127
-- FLOAT, DECIMAL, FLOAT, DECIMAL, DECIMAL, DECIMAL
--, 59, 60, 62, 106, 108, 122, 127
)
--\-----------------------------------------------------------------------------------
---) INITIALIZATION
---) Specify the texts to search for below “INSERT INTO @search_values”
---) Also set a value for @max_difference
---) @max_difference makes it possible to find nearby numbers.
--/-----------------------------------------------------------------------------------
SELECT @max_difference = 1.0
INSERT INTO @search_values
SELECT 755
--UNION SELECT 97867563
DECLARE search_cursor CURSOR LOCAL STATIC FOR
SELECT search_value
FROM @search_values
-------------------------------------------------------------------------------------
-- MAIN
-------------------------------------------------------------------------------------
OPEN columns_cursor
FETCH NEXT FROM columns_cursor INTO @schema_name, @table_name, @column_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @debug_print = 'Processing ' + @table_name + '.' + @column_name;
PRINT REPLICATE('-', LEN(@debug_print))
PRINT @debug_print
PRINT REPLICATE('-', LEN(@debug_print))
SET @nsql = 'INSERT INTO #search_results SELECT DISTINCT @inner_search_value, '
+ '''' + @schema_name + ''' AS [schema_name], '
+ '''' + @table_name + ''' AS [table_name], '
+ '''' + @column_name + ''' AS [column_name], CONVERT(DECIMAL(38, 2), ' + QUOTENAME(@column_name) + ') AS [the_value] '
+ ' FROM ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name) + ' WITH (NOLOCK) WHERE ABS(' + QUOTENAME(@column_name) + ' - @inner_search_value) <= ' + CAST(@max_difference AS VARCHAR(50)) + ';';
PRINT @nsql
OPEN search_cursor
FETCH NEXT FROM search_cursor INTO @search_value
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ''
PRINT '--> Processing search value ''' + CONVERT(NVARCHAR(40), @search_value) + '''';
SET @param_def = N'@inner_search_value DECIMAL(38, 2)';
EXECUTE sp_executesql @nsql, @param_def,
@inner_search_value = @search_value;
FETCH NEXT FROM search_cursor INTO @search_value
END
CLOSE search_cursor
FETCH NEXT FROM columns_cursor INTO @schema_name, @table_name, @column_name
SELECT @results_count = (SELECT COUNT(*) FROM #search_results);
END
CLOSE columns_cursor
DEALLOCATE columns_cursor
SELECT
t.schema_name
, t.table_name
, t.column_name
, t.the_value
, 'SELECT * FROM ' + QUOTENAME(t.schema_name) + '.' + QUOTENAME(t.table_name) + ' WHERE ' + QUOTENAME(t.column_name) + ' = ''' + REPLACE(t.the_value, '''', '''''') + ''''
FROM #search_results t
GO
