Search All Fields in db for a Specific String
forums question came up where someone needed to search every varchar/char field in the database, and find any table/field that had a specific value (ie ip address, email, servername)
we all agree that a monster scan like this is bad, but bad or not, sometimes you need to do it.
it returns results that look something like this:
TBLNAME COLNAME SQL
CITYDATA CITYNAME SELECT * FROM CITYDATA WHERE CITYNAME LIKE 'TEST'
GMHUDMX HUDMXTITLE SELECT * FROM GMHUDMX WHERE HUDMXTITLE LIKE 'TEST'
RB_FIELD FIELD_ALIAS SELECT * FROM RB_FIELD WHERE FIELD_ALIAS LIKE 'TEST'
RB_FIELD FIELD_NAME SELECT * FROM RB_FIELD WHERE FIELD_NAME LIKE 'TEST'
DROP PROCEDURE UGLYSEARCH
go
CREATE PROCEDURE UGLYSEARCH
-- EXEC UGLYSEARCH 'TEST'
-- EXEC UGLYSEARCH 'TEST',1
@SEARCHSTRING VARCHAR(50),
@SEARCHBYCOLUMN INT = 0
AS
SET NOCOUNT ON
DECLARE @SQL VARCHAR(8000),
@TABLENAME VARCHAR(60),
@COLUMNNAME VARCHAR(60),
@COLZ VARCHAR(7000)
CREATE TABLE #RESULTS(TBLNAME VARCHAR(60),COLNAME VARCHAR(60),SQL VARCHAR(7800))
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')
AND SYSCOLUMNS.LENGTH >= LEN(@SEARCHSTRING)
ORDER BY TBLNAME,COLNAME
IF @SEARCHBYCOLUMN = 0
BEGIN
DECLARE C1 CURSOR FOR
SELECT DISTINCT TBLNAME FROM #FKFINDER ORDER BY TBLNAME
OPEN C1
FETCH NEXT FROM C1 INTO @TABLENAME
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @COLZ=''
SELECT @COLZ= @COLZ + COLNAME + ' LIKE ''%' + @SEARCHSTRING + '%'' OR ' FROM #FKFINDER WHERE TBLNAME=@TABLENAME
--@COLZ has a trailing 'OR ' which must be removed
SET @COLZ = SUBSTRING(@COLZ,1,LEN(@COLZ) -3)
--PRINT @COLZ
SET @SQL = 'IF EXISTS(SELECT * FROM ' + @TABLENAME + ' WHERE ' + @COLZ + ') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',''-'','' SELECT * FROM ' + @TABLENAME + ' WHERE ' + REPLACE(@COLZ,'''','''''') + ''') ;'
--PRINT @SQL
EXEC (@SQL)
FETCH NEXT FROM C1 INTO @TABLENAME
END
CLOSE C1
DEALLOCATE C1
END --IF
ELSE
BEGIN
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 @COLZ=''
SELECT @COLZ= @COLZ + COLNAME + ' LIKE ''%' + @SEARCHSTRING + '%'' OR ' FROM #FKFINDER WHERE TBLNAME=@TABLENAME
--@COLZ has a trailing 'OR ' which must be removed
SET @COLZ = SUBSTRING(@COLZ,1,LEN(@COLZ) -3)
--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
END --ELSE
SELECT * FROM #RESULTS 
