find specific text in the db
-
i am looking for specific text in a sql 2005 db. however I dont know the table name or field name which contains the text. is there anyway I can search for this content in the db?
thanks
-
There is a scrips section in this website with tons of such scripts. check'em out.
******************
Dinakar Nethi
Life is short. Enjoy it.
****************** -
If the text you are looking for is in the DDL of your objects, you can use SqlSpec to generate a chm for the db. The chm indexes all the DDL in the database so you can search for any string.
SqlSpec is a shareware app I wrote, the link to it is in my sig below.
---------------------------------------
elsasoft.org -
Infact I am looking for a value residing in some column of some table .
-
This SQL will do what you want, and it is reasonably quick. We use it to search DBs up to 3G, and it takes less than a minute on a slow server:
/*
* Enter the data you want to search for in the @strSearchData variable declared below.
* 'Smith' is just an example. There is no need to surround it with % signs as they are automatically added.
*/
DECLARE @strSearchData varchar (200) SET @strSearchData = 'Smith'
DROP TABLE #Tbnames
DROP TABLE #Fields
DECLARE @SQL varchar (500)
CREATE TABLE #tbnames (
dbid int null,
tbname varchar (100)
 
insert into #tbnames
Select ID, name
from sysobjects where xtype = 'U' and name like 'tbl%'
CREATE TABLE #Fields(
FieldID int IDENTITY (1,1),
intYesNo int not null default 0,
dbid int ,
fname varchar (100),
tStart int,
tEnd int
 
insert into #Fields (dbid, fname)
Select ID, name
from syscolumns sc
INNER JOIN #tbnames tn ON sc.ID = tn.dbID
where sc.xtype IN (175,239,231,167)
--Datatypes: char 175, nchar 239, nvarchar 231, varchar 167
DECLARE stc CURSOR FOR
select tbname, fname, FieldID from #tbnames tn
INNER JOIN #fields fn ON tn.dbid = fn.dbid
DECLARE @tname varchar (100)
DECLARE @fname varchar (100)
DECLARE @FieldID int
OPEN stc
FETCH NEXT FROM stc
INTO @tname, @fname, @fieldID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'IF (SELECT count(*) FROM ' + @tname
SET @SQL = @SQL + ' WHERE ' + @fname + ' like (''%' + @strSearchData + '%'')) > 0 '
SET @SQL = @SQL + ' UPDATE #Fields SET intYesNo = 1 WHERE FieldID = ' + cast(@FieldID as varchar)
EXEC (@SQL)
FETCH NEXT FROM stc
INTO @tname, @fname, @fieldID
END
CLOSE stc
DEALLOCATE stc
--Now show results
select tbname, fname from #tbNames tn
INNER JOIN #fields fd ON tn.dbid = fd.dbid
WHERE intYESNO > 0
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply