Search specific string in data in all tables

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Search specific string in data in all tables

Search specific string in data in all tables

Rate Topic

Display Mode

Topic Options

Author

Message

joemai

Posted Tuesday, November 06, 2007 12:26 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, May 20, 2013 12:26 PM
Points: 242, Visits: 201

Hi Experts,
Is there a way to find specific string in data in all the user tables, not column names or stored procedures? Can some one please help? Also, one of our websites was hacked by someone. I believe that they use SQL injection to do so. Is there anyway/tool to check for SQL vulnerbilities?

Any inputs will be very appreciated.

Thanks,
Minh

Post #419243

Venkataraman-408293

Posted Tuesday, November 06, 2007 12:55 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 17, 2011 1:23 AM
Points: 132, Visits: 122

This one is useful link to search and replace SQL Server data in all columns of all tables, in a given database?

http://vyaskn.tripod.com/sql_server_search_and_replace.htm
:)

Post #419254

Lowell

Posted Tuesday, November 06, 2007 1:35 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 2:29 PM
Points: 11,645, Visits: 27,733

here's something i use, and i'm sure i'm stating the obvious and what you already did tog et back up and running, but you'd be best off doing a back up, restoring a previous database, and then compare corrupted vs production.

there's no built in tool to test for sql injection, you;l need to go thru your code.

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #419285

edylee

Posted Monday, June 13, 2011 10:28 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, June 14, 2011 5:05 PM
Points: 1, Visits: 2

Here is a tool you can try (Free).
http://www.sqlmgmt.com/ProductDetail.aspx?Id=101

This tool helps you to search all columns of all tables in a database for a string keyword. In order to increase performance, it examines string type columns only in all tables to search for a given keyword.

Post #1124732

regeter

Posted Monday, December 10, 2012 10:54 AM

Forum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 11:45 AM
Points: 7, Visits: 83

I got something similar which is just a plain query, not a tool or a sProc.
[url=http://fullparam.wordpress.com/2012/09/07/fck-it-i-am-going-to-search-all-tables-all-collumns/][/url]

Post #1394713

« Prev Topic | Next Topic »

Permissions