Find any value in a database

Sometimes you just need to find a value in a database without knowing its exact location. This script will find any value, restricted to VARCHAR, NVARCHAR and CHAR columns. When you use it, you can ofcourse easy tune this script to include all types, for my case i needed only these “textual” datatypes.

The script can be downloaded at the bottom of this post.

if object_id('spSysFindValuesInDatabase') is not null
begin
  drop procedure spSysFindValuesInDatabase
end
go
create procedure spSysFindValuesInDatabase (
  @value varchar(max)
)
as
set nocount on
------------------------------------------------------
-- Declare local variables
------------------------------------------------------
declare @nsql nvarchar(max)
      , @totrow int
      , @currow int
------------------------------------------------------
-- Create Statements temp table
------------------------------------------------------
declare @SqlStatements as table (
  Id int identity(1, 1)
, SqlStatement varchar(500)
)
------------------------------------------------------
-- Create results temp table
------------------------------------------------------
declare @result as table (
  TableName varchar(100)
, ColumnName varchar(100)
)
------------------------------------------------------
-- Insert the generated statements into the temp table
-- The logic of columns to find is put in this query
------------------------------------------------------
insert
into    @SqlStatements
select  replace(replace(replace(
          'select  ''[TABLE]''
                 , ''[COLUMN]'' 
           from    [TABLE] 
           where   [COLUMN] like ''%[VALUE]%'' '
        , '[TABLE]', t.name)
        , '[COLUMN]', c.name)
        , '[VALUE]', @value)
from    sys.tables t
        inner join sys.columns c
          on  c.object_id = t.object_id
        inner join sys.types y
          on  y.system_type_id = c.system_type_id 
          and y.user_type_id = c.user_type_id
          and y.name in ('varchar', 'nvarchar', 'char')
------------------------------------------------------
-- Set min and max values for looping
------------------------------------------------------
set @totrow = @@rowcount
set @currow = 1
while @totrow > 0 and @currow <= @totrow
begin
  ----------------------------------------------------
  -- Get a statement from the temp table
  ------------------------------------------------------
  select  @nsql = SqlStatement
  from    @SqlStatements
  where   Id = @currow
  ----------------------------------------------------
  -- Insert the results in the temp table
  ----------------------------------------------------
  insert
  into    @result
  exec sp_executesql @nsql
  ----------------------------------------------------
  -- Get next record
  ----------------------------------------------------
  set @currow = @currow + 1
end          
------------------------------------------------------
-- Output the result
------------------------------------------------------
select  *
from    @result

go

How it works?

As you can see in the script we generate some dynamic T-SQL code, where the table, column and value are inserted. It is generated over all the columns in all tables, where the datatypes are VARCHAR, NVARCHAR or CHAR. You can remove that join if you want to query over all columns, however, be carefull with numeric types and textual seek values.

Next we loop through the generated results, where we execute the statements and put the result in a temp table. In the end we will return these results. And that’s it, quite easy if you ask me. You can download the script right here :? spSysFindValuesInDatabase.sql

When to use?

A script like this one may look handy at some times, but always make sure you don’t run this on a production system, since it can give you table scans/index scans, which are bad! So try to use this on a development or test environment where you are sure you are not hurting the business with bad performance.

 

Have fun :)

Leave a Reply

  

  

  

* Copy This Password *

* Type Or Paste Password Here *

Switch to our mobile site

%d bloggers like this: