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