September 11, 2009 at 10:28 am
As the description reads, I need to search every column in a database, I've done some web searchs and found a few scripts that look for specifc strings, but I need to put some logic on the search paramaters. Does anyone have something they've already written to do this?
I'd really appreciate it.
September 11, 2009 at 10:48 am
Were you loking for this 🙂
Use master
sp_msforeachdb "select column_name from ?.INFORMATION_SCHEMA.COLUMNS"
Hope this helps
Regards
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
September 11, 2009 at 11:12 am
Perhaps that's what I need, though I might be missing the point on how I'd use it. I need to search through the rows in every column in the db for a string, not the columns names themselves. I put an information_schema.columns query together but all that does is return every column in the db. I don't need the sp_MSforeachdb sp in that case:
use dbname
go
select * from information_schema.columns
Maybe you can explain it to me, sometimes I am a bit slow... 🙂
September 11, 2009 at 11:17 am
Some words come to mind.
Cursor. Dynamic SQL. Slow. Why?
You can use the field names from the information_schema view, use it as a cursor and dynamic sql to go through them searching for your string (I'd at least filter out the ones that aren't the right datatype).
Just because you *can* though, doesn't mean you *should*. Is this a one time thing where you need to find some piece of lost information / find out where something is stored, or were you looking to put this into a piece of re-usable code. If this is something that will run on more than exceptionally rare occasions, I would strongly suggest you find a different way.
I can't think of any ways to make something like this "good", but you can do a few things to make it less bad, such as grabbing all the fields you want to search out of each table so that you only table scan it once rather than once per column.
September 11, 2009 at 11:24 am
So I left out some of the details in the hopes someone already has something written for this. I am aware of the resources this is going to take, and have restored a copy of the database to a dev server. I was mainly hoping I wouldn't have to spend the time to come up with a manual way of doing this as time is in short supply at the moment, but it looks like no one has something already written. This isn't a permenant piece of code, but it will need to be run repeatedly over the next few weeks with different search paramaters.
I am looking for credit card info, that might have been entered into either a number or a text datatype. Most likely a string but not necessarily.
If anyone has a good method on how to do this, I'd really appreciate it, because unforunately, due to legal reasons, this has to be done.
September 11, 2009 at 11:39 am
See if this is what you are looking for.
http://www.sqlservercentral.com/scripts/Miscellaneous/30659/
September 11, 2009 at 11:46 am
Understood. Sorry if I sounded preachy, just had to check. Best of Luck in your search, unfortunately I have no such script handy.
September 11, 2009 at 11:59 am
Matt, I actually found and tried that script, but it doesn't seem to have the ability to add wildcards, and it also doesn't gracefully handle the poorly formed tables names this vendor db uses even with a set of [].
The closest thing I've found so far is this guy:
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
If this would work with wildcards I'd be in luck. I think what i'll have to do is wrap this sp into something dynamic as suggested above. Again, I was just hoping someone had something already written so I could save some development time (that I don't have).
September 11, 2009 at 12:15 pm
here's my version:
it returns a dataset of what matched, so you can drill down to it:
--ok, really you only need to search columns that are of type
--varchar,char,nvarchar and ntext....
--you obviously wouldn't need to search a numeric field for a string.
--here's a handy procedure...but BEWARE...it can bring your server to it's knees!
--NEVER run this on production...make a copy, and punish your development server instead.
--here's why it's bad: if you have a table with a million/billion rows,
--and that table has 20 varchar/char columns, it will search
--the million/billion row table 20 times...once for each column.
--and every one of them will be a TABLE SCAN because it won't be using indexes for a LIKE statement.
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 = '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
September 11, 2009 at 12:22 pm
Lowell, I think this might work!
So basically I just modify the @sql variable's text a bit and change the paramater going into the stored procedure to do the searches?
Thanks I'll give this a try right now.
September 11, 2009 at 12:37 pm
as far as i know, it needs no modifications.
if you needed to search for "bill@somewebsite.com" for example, it would just be
EXEC UGLYSEARCH 'bill@somewebsite.com'
maybe i missed that you had an additional requirement? what did you mean by wildcards in your latest post? be specific? what wildcard string would you search for?
anything that you can find with LIKE can be fed to the proc, ie ' PR[A-Z,0-9][A-Z,0-9]'
would find anthing that had a space, "PR",and two more characters
Lowell
September 11, 2009 at 2:18 pm
I think that your procedure will work, I need to search for partial credit card strings
so perhaps 1234________5678
where the underscore is any character be it with dashes or without dashes.
So I think your proecure will work great.
on a side note, I can't seem to get your script to run, it's bailing on the terrible table names this db uses, they are like this: _SCHEMA_._TABLENAME_
I'm going to take a look at it this afternoon and see if I can get some more insight into the issue.
probably it just needs [] around the names.
edit:
here is the error with an example table name
IF EXISTS(SELECT * FROM [_ACTIONS_] WHERE LIKE '%TEST%') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES('_ACTIONS_','CODE',' SELECT * FROM [_ACTIONS_] WHERE LIKE ''%TEST%''') ;
Msg 208, Level 16, State 1, Line 1
Invalid object name '_ACTIONS_'.
September 11, 2009 at 2:40 pm
Keep in mind that because the sp uses dynamic SQL, any wildcards you send in with your text string should work as wildcards.
IE. EXEC UGLYSEARCH '1234%5678'
or
EXEC UGLYSEARCH '1234%[0-9][0-9][0-9][0-9]%[0-9][0-9][0-9][0-9]%5678'
etc. etc.
Also, I believe if the _'s on your table were your issue, it would be a syntax error. The invalid object name is more likely a database or schema issue.
You actually don't even need to wrap a table like _ACTIONS_ in brackets.
This is a pretty handy script. You're a treasure trove of these things Lowell. 🙂
September 11, 2009 at 3:20 pm
You're absolutely right, I wrapped that value in [] and it didn't affect it at all, same error, so would it then be that it doesn't know the schema?
I'm just running it with TEST for now. Nothing fancy yet.
And yes, this script is awesome. 🙂
September 11, 2009 at 3:30 pm
Yeah, this script is only supplying the table name, not the fully qualified servername.databasename.schema.tablename.
You can usually get away with not specifying the first 3, unless you're attempting to run it in one database/server and access objects in another, or you are using a schema other than dbo. My guess is the schema is the issue here.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply