How do I wildcard search all columns in a database?

Question

How do I wildcard search all columns in a database?

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

mortalic SSCrazy Points: 2068 More actions · Answer 1

forgive me, I still have questions...

Where is the schema information stored? It's not in sysobjects that I can tell.

I went through these tables, and it looks like there are lot's of schemas I'll have to go through, and only a few are dbo (<20).

Lowell SSC Guru Points: 323518 More actions · Answer 2

Seth thank you for the compliment! it means a lot coming from someone like yourself, who spends a lot of time trying to help folks here.

i guess the issue was you needed to know which schema might have returned results; try this version, which includes the schema.

i changed this to use 2005/2008 sys,object views, instead of the backward compatibles.

see if this is what you were after:

--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.

ALTER PROCEDURE UGLYSEARCH

-- EXEC UGLYSEARCH 'TEST'

@SEARCHSTRING VARCHAR(50)

AS

BEGIN --PROC

SET NOCOUNT ON

DECLARE @SQL VARCHAR(500),

@SCHEMANAME VARCHAR(60),

@TABLENAME VARCHAR(60),

@COLUMNNAME VARCHAR(60)

CREATE TABLE #RESULTS(SCHEMANAME VARCHAR(60),TBLNAME VARCHAR(60),COLNAME VARCHAR(60),SQL VARCHAR(600))

SELECT

SCHEMA_NAME(schema_id) AS SCHEMANAME,

SYS.OBJECTS.NAME AS TBLNAME,

SYS.COLUMNS.NAME AS COLNAME,

TYPE_NAME(SYS.COLUMNS.system_type_id) AS DATATYPE

INTO #FKFINDER

FROM SYS.OBJECTS

INNER JOIN SYS.COLUMNS ON SYS.OBJECTS.object_id=SYS.COLUMNS.object_id

WHERE SYS.OBJECTS.type='U'

AND TYPE_NAME(SYS.COLUMNS.system_type_id) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')

ORDER BY TBLNAME,COLNAME

DECLARE C1 CURSOR FOR

SELECT SCHEMANAME,TBLNAME,COLNAME FROM #FKFINDER ORDER BY SCHEMANAME,TBLNAME,COLNAME

OPEN C1

FETCH NEXT FROM C1 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME

WHILE @@FETCH_STATUS -1

BEGIN

SET @SQL = 'IF EXISTS(SELECT * FROM ['

+ @SCHEMANAME + '].['

+ @TABLENAME + '] WHERE ['

+ @COLUMNNAME + '] LIKE ''%'

+ @SEARCHSTRING + '%'') INSERT INTO #RESULTS(SCHEMANAME,TBLNAME,COLNAME,SQL) VALUES('''

+ @SCHEMANAME + ''','''

+ @TABLENAME + ''','''

+ @COLUMNNAME + ''','' SELECT * FROM ['

+ @SCHEMANAME + '].['

+ @TABLENAME + '] WHERE ['

+ @COLUMNNAME + '] LIKE ''''%'

+ @SEARCHSTRING + '%'''''') ;'

PRINT @SQL

EXEC (@SQL)

FETCH NEXT FROM C1 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME

END

CLOSE C1

DEALLOCATE C1

SELECT * FROM #RESULTS

END --PROC

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mortalic SSCrazy Points: 2068 More actions · Answer 3

Lowell, it works great, does exactly what I need, you my friend are a saviour!

Both of you, thank you for all your help today. I really appreciate it.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Lowell (9/11/2009)
Seth thank you for the compliment! it means a lot coming from someone like yourself, who spends a lot of time trying to help folks here.
i guess the issue was you needed to know which schema might have returned results; try this version, which includes the schema.
i changed this to use 2005/2008 sys,object views, instead of the backward compatibles.
see if this is what you were after:
--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.
ALTER PROCEDURE UGLYSEARCH
-- EXEC UGLYSEARCH 'TEST'
@SEARCHSTRING VARCHAR(50)
AS
BEGIN --PROC
SET NOCOUNT ON
DECLARE @SQL VARCHAR(500),
@SCHEMANAME VARCHAR(60),
@TABLENAME VARCHAR(60),
@COLUMNNAME VARCHAR(60)
CREATE TABLE #RESULTS(SCHEMANAME VARCHAR(60),TBLNAME VARCHAR(60),COLNAME VARCHAR(60),SQL VARCHAR(600))
SELECT
SCHEMA_NAME(schema_id) AS SCHEMANAME,
SYS.OBJECTS.NAME AS TBLNAME,
SYS.COLUMNS.NAME AS COLNAME,
TYPE_NAME(SYS.COLUMNS.system_type_id) AS DATATYPE
INTO #FKFINDER
FROM SYS.OBJECTS
INNER JOIN SYS.COLUMNS ON SYS.OBJECTS.object_id=SYS.COLUMNS.object_id
WHERE SYS.OBJECTS.type='U'
AND TYPE_NAME(SYS.COLUMNS.system_type_id) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
ORDER BY TBLNAME,COLNAME
DECLARE C1 CURSOR FOR
SELECT SCHEMANAME,TBLNAME,COLNAME FROM #FKFINDER ORDER BY SCHEMANAME,TBLNAME,COLNAME
OPEN C1
FETCH NEXT FROM C1 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME
WHILE @@FETCH_STATUS -1
BEGIN
SET @SQL = 'IF EXISTS(SELECT * FROM ['
+ @SCHEMANAME + '].['
+ @TABLENAME + '] WHERE ['
+ @COLUMNNAME + '] LIKE ''%'
+ @SEARCHSTRING + '%'') INSERT INTO #RESULTS(SCHEMANAME,TBLNAME,COLNAME,SQL) VALUES('''
+ @SCHEMANAME + ''','''
+ @TABLENAME + ''','''
+ @COLUMNNAME + ''','' SELECT * FROM ['
+ @SCHEMANAME + '].['
+ @TABLENAME + '] WHERE ['
+ @COLUMNNAME + '] LIKE ''''%'
+ @SEARCHSTRING + '%'''''') ;'
PRINT @SQL
EXEC (@SQL)
FETCH NEXT FROM C1 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME
END
CLOSE C1
DEALLOCATE C1
SELECT * FROM #RESULTS
END --PROC

Lowell,

This problem occurs often enough... you should write an article on it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mortalic SSCrazy Points: 2068 More actions · Answer 5

Lowell, one quick question for you, I've been using this a bit this morning and the sql that it's generating for the sql varchar is displaying like this:

SELECT * FROM [_SMDBA_].[_EMAILMGMT_] WHERE [SUBJECT] LIKE '4[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9'

Notice the last numeric wildcard is missing it's bracket, and there are only 10 numbers whereas I'm feeding it 16:

EXEC UGLYSEARCH '4[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

I've looked through your script but I can't find anything that would chop off the length like that.

Think you can't help me out again?

Thanks!

mortalic SSCrazy Points: 2068 More actions · Answer 6

Oh, nevermind, I see the var @searchstring is length 50. my mistake, I can adjust that.

False alarm! 🙂

Lowell SSC Guru Points: 323518 More actions · Answer 7

doh! i was looking for that kind of logic hole, too small-o-variable, glad you found it!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!