September 11, 2009 at 3:56 pm
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).
September 11, 2009 at 4:11 pm
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
September 11, 2009 at 4:24 pm
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.
September 11, 2009 at 8:31 pm
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
September 14, 2009 at 11:45 am
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!
September 14, 2009 at 11:50 am
Oh, nevermind, I see the var @searchstring is length 50. my mistake, I can adjust that.
False alarm! 🙂
September 14, 2009 at 2:26 pm
doh! i was looking for that kind of logic hole, too small-o-variable, glad you found it!
Lowell
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply