Monday, June 23, 2014

Get Records from ANY table in a Database Matching Specified Value

Overview
§  Transact SQL; TSQL; Search entire database for a value
§  This script/stored procedure when created, then executed, will search every table in the database it has been created on, for the search value the user passes it (can handle wildcard
[edit]
Instructions
1.     EDIT the use quotemaster statement to refer to your own database, i.e. use Northwind;
2.     Then, uncomment the use and go statements immediately following the BEGIN SCRIPT line
3.     Then, select and run (can highlight and press F5 in Query Analyzer) all lines from BEGIN SCRIPT to END SCRIPT (end of this page)
4.     To see it in action, once you have done steps 1 - 3 above.
1.     Edit the database name in the USAGE section below, to refer to your own database
2.     Edit the '%FUELSC%' search term param value (below) with the search term you are looking for---you CAN use wildcards
3.     Select the three lines in the USAGE section below, and execute (can press F5 to execute highlighted text in Query Analyzer)
[edit]
Note
A large database could take several minutes to search through
§  USAGE (once stored proc has been created in your db):
§  use quotemaster; --replace with database name you are using, and UNCOMMENT
§  go --UNCOMMENT
§  exec dbo.GetRecordsWithValInAnyFld '%FUELSC%'; --replace paramater value with search term, and UNCOMMENT
§  BEGIN SCRIPT
§  use quotemaster; --EDIT THIS LINE, TO REFER TO THE DATABASE YOU WILL PLACE SCRIPT IN --go --THEN, UNCOMMENT THIS LINE (go), AND RUN all FROM PREVIOUS LINE To END
[edit]
Code
if exists
(select from sysobjects
      where id = object_id(N'dbo.GetRecordsWithValInAnyFld')
      and OBJECTPROPERTY(id, N'IsProcedure') = 1)
      drop proc dbo.GetRecordsWithValInAnyFld;
go

create procedure dbo.GetRecordsWithValInAnyFld
(@ValToFind varchar(500))
AS
-----------------------------------------------------------------------------------------------------
-- Procedure Name-     dbo.GetRecordsWithValInAnyFld()
--
-- Function Gets Columns/Fields that belong to name passed in
-- Wildcard (%) can be used
-- Input Params @ValToFind varchar(500): term (can use wildcards) to match more loosely
-- Output Params Recordset
-- Return Value 1 => If Successful
-- number => In case of error return the error number with text
-- Date 09/28/2005
-- Author Aendenne C. Armour
-- Modification Log:
-- Version___Modified by_________Mod Date___Summary of Modifications

-- 1.0_______Aendenne C. Armour__9/28/05_____None: Initial Version
-- x.0_______Aendenne C. Armour__4/2/08______To fix prior edits, and to account for other data types
-----------------------------------------------------------------------------------------------------
declare @RowCount int, @Error int, @errTxt varchar(255);
select @RowCount = 0, @Error = 0;

declare @objName varchar(150);
set @objName = 'dbo.GetRecordsWithValInAnyFld';

set nocount on;
set transaction isolation level read uncommitted;

create table #dbTbls
(
      TblName varchar(300)
);


insert into #dbTbls
      (TblName)
      select o.[Name] as TblName
      from sysobjects o with(nolock)
      where o.type = 'U'
      order by TblName;

create table #TblFlds
(
      TblName varchar(300),
      FldName varchar(300)--,
      --DataType varchar(100)
);

insert into #TblFlds
      (TblName, FldName)--, DataType)
      select o.name as TblName, '[' + c.name + ']' as FldName    --,o.Type as ObjTypeAbbr, t.Name as DataType
      from syscolumns c with(nolock)
      inner join sysobjects o on c.id = o.id
      inner join systypes t on c.xusertype = t.xusertype
      where o.type = 'U' and t.name in ('varchar''char''nvarchar''nchar')
      order by TblName, FldName;

declare @tbl varchar(300), @fld varchar(300);
declare @where varchar(7000);
declare @sql varchar(8000);

create table #tmpVal
(
      val varchar(300)
);

--select * from #dbTbls
--select * from #TblFlds

while exists(select TblName from #dbTbls)
begin
      set @where = ' where ';
      insert into #tmpVal
            (val)
            select top 1 TblName as val from #dbTbls;
      select @tbl = val from #tmpVal;

      truncate table #tmpVal;

      set @fld = null;
      while exists(select FldName from #TblFlds where TblName = @tbl)
      begin
            insert into #tmpVal
                  (val)
                  select top 1 FldName from #TblFlds where TblName = @tbl;

            select @fld = val from #tmpVal;
            truncate table #tmpVal;

            set @where = @where + @fld + ' like + @ValToFind + or '
            delete from #TblFlds where TblName = @tbl and FldName = @Fld;
      end

      if @fld is not null
      begin
            set @where = left(@where, len(@where) - 3); --remove last OR
            --got where clause, get records:
            set @sql = 'if (select count(*) from ' + @tbl + ' ' + @where + ') > 0';
            set @sql = @sql + ' select + @tbl + as TblName, * from ' + @tbl + ' ' + @where;
            if len(@sql) >= 7999 set @sql = 'select ' + @tbl + ' as TblName, CHECK TABLE MANUALLY; TOO MANY COLUMNS as WARNING';
            exec(@sql);
            --print @sql;
      end
      delete from #dbTbls where TblName = @tbl;

      if (select count(TblName) from #dbTbls) <= 0
            break;
end

--select * from #tmpVal;

drop table #dbTbls;
drop table #TblFlds;
drop table #tmpVal;

select @Error = @@error, @RowCount = @@rowcount;
if @Error <> 0
      begin
            set @errTxt = 'Exception: ' + @objName + ' occured; Main Select Statement';
            raiserror(@errTxt, 1, 2);
      end
--

go

grant exec on dbo.GetRecordsWithValInAnyFld to public;

go