May 17, 2006 at 9:15 am
well I have a interesting scenario. I need to search for a specific value in the database. However I dont have a tablename or columnname. Is there anyway to accomplish what I am trying to do.
I know this query will kill the performance.
thanks
May 17, 2006 at 9:41 am
Yes, you can write a procedure to do. It should take around 1-2 hours to write.
Loop throught each database and then throught each table. Dynamically create sql statement to search all the columns (you can limit the column type to char , varchar , etc)
and execute each query. Performance will be bad incase you have large database.
Amit Lohia
May 17, 2006 at 9:46 am
You can query sysobjects and syscolumns. Here's an example for a value of type varchar:
declare @table table (id int identity(1,1),tbl varchar(55),col varchar(55))
insert into @table
select so.name as
, sc.name as [column]
from sysobjects so inner join syscolumns sc on so.id=sc.id
where so.xtype='U' and sc.xtype=167
order by so.name
declare @rowcount int, @rownum int, @tbl varchar(55), @col varchar(55)
declare @sql varchar(255)
set @rownum=1
select @rowcount=count(id) from @table
while (@rownum<=@rowcount)
begin
select @col=col, @tbl=tbl from @table where id=@rownum
set @sql='select [' +@col+ '] from ['+@tbl+ '] where '+@col+ '=''<value>'''
exec (@sql)
if @@rowcount>0
print 'Value Found in table:'+@tbl+' column:'+@col
set @rownum=@rownum+1
end
May 17, 2006 at 11:38 am
monster searches like this are bad, but sometimes you gotta do it:
I made an ugly server intensive cursor for a similar question a while back;noone came up with a better example so far.
see the thread here:
it whips through every varchar column in the database and searches for the searchstring....
note that this would be ok for a developer, but something like this in production could severly impact perfromance, especially if some table had a million rows in it, and that table had a lot of varchar columns to be searched.
CREATE PROCEDURE UGLYSEARCH
-- EXEC UGLYSEARCH 'TEST'
@SEARCHSTRING VARCHAR(50)
AS
SET NOCOUNT ON
DECLARE @sql VARCHAR(500),
@TABLENAME VARCHAR(60),
@COLUMNNAME VARCHAR(60)
CREATE TABLE #RESULTS(TBLNAME VARCHAR(60),COLNAME VARCHAR(60),SQL VARCHAR(600))
SELECT
SYSOBJECTS.NAME AS TBLNAME,
SYSCOLUMNS.NAME AS COLNAME,
TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE
INTO #FKFINDER
FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID
WHERE SYSOBJECTS.XTYPE='U'
AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
ORDER BY TBLNAME,COLNAME
DECLARE C1 CURSOR FOR
SELECT TBLNAME,COLNAME FROM #FKFINDER ORDER BY TBLNAME,COLNAME
OPEN C1
FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME
WHILE @@FETCH_STATUS <> -1
BEGIN
--SET @sql = 'SELECT ''' + @TABLENAME + ''' AS TABLENAME,''' + @COLUMNNAME + ''' AS COLUMNNAME,* FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE ''%' + @SEARCHSTRING + '%'''
SET @sql = 'IF EXISTS(SELECT * FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE ''%' + @SEARCHSTRING + '%'') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',''' + @COLUMNNAME + ''','' SELECT * FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE ''''' + @SEARCHSTRING + ''''' '') ;'
PRINT @sql
EXEC (@SQL)
FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME
END
CLOSE C1
DEALLOCATE C1
SELECT * FROM #RESULTS
Lowell
May 17, 2006 at 11:41 am
well I used the following and it works really well. All you have to do is replace the value in third line with what you are looking for... cheers
SET NOCOUNT ON
DECLARE @Missing varchar(100)
SET @Missing = 'RMOOREXP2'
--Searches for 'abc'
SELECT Haystack = TABLE_NAME
INTO Pitchfork
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
SELECT Haystack, COLUMN_NAME AS Needle
INTO Iowa
FROM Pitchfork JOIN INFORMATION_SCHEMA.COLUMNS
ON TABLE_NAME = Haystack
AND
(DATA_TYPE LIKE '%char' or
DATA_TYPE like '%text')
--)
CREATE TABLE PigsEar (SilkPurse text)
DECLARE @FarmQuery varchar(1000)
SET @FarmQuery =
'INSERT INTO PigsEar SELECT TOP 1 SilkPurse=''Found ' +
REPLACE(@Missing,'''','''''') + ' in HAYSTACK.NEEDLE'' FROM HAYSTACK WHERE ' +
--'NEEDLE = ' + QUOTENAME(@Missing,'''') --use this one if you are looking for whole field value
'NEEDLE LIKE ' + QUOTENAME(@Missing,'''') --use this one if you are looking for a part of a field value
--Leave it as is if you want to find the string as an exact match of a
DECLARE @Murderer varchar(1000)
DECLARE @Haystack sysname
DECLARE @Needle sysname
DECLARE Investigation CURSOR FOR
SELECT Needle, Haystack FROM Iowa
WHERE Haystack <> 'Pitchfork'
OPEN Investigation
DECLARE @miracle int
FETCH NEXT FROM Investigation INTO @Needle,@Haystack
WHILE @@FETCH_STATUS = 0 BEGIN
SET @Murderer =
REPLACE(REPLACE(@FarmQuery,'NEEDLE',quotename(@Needle)),
'HAYSTACK',quotename(@Haystack))
exec (@Murderer)
SELECT @miracle = COUNT(left(cast(SilkPurse as varchar(8000)),8000)) FROM PigsEar
FETCH NEXT FROM Investigation INTO @Needle,@Haystack
END
IF @miracle IS NULL
PRINT '['+@Missing+'] not found'
ELSE
SELECT SilkPurse FROM PigsEar
DEALLOCATE Investigation
go
DROP TABLE Pitchfork
DROP TABLE Iowa
DROP TABLE PigsEar
May 17, 2006 at 12:29 pm
That solution looks good, but you are still querying all columns regardless of the datatype they contain. If you know the value you're looking for, you know the datatype and by hardcoding that in the script you can take a big load off the server.
I tried the script I wrote this morning and it worked just fine. The only two things you need to replace are the datatype and the value you're searching for.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply