June 29, 2012 at 3:12 pm
Hi there,
Had interesting task today, to seach sps / views in a db for any containing '*' character.
So far I have tried the following
SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%!*%' ESCAPE '!'
GROUP BY OBJECT_NAME(id)
But it still just returns all objects, that definitely do not contain '*' I was hoping the ESCAPE would do the trick, but no luck. Anyone have any suggestions?
Thank you kindly!
GP
June 29, 2012 at 3:14 pm
Use CHARINDEX() > 0. Don't use PATINDEX though, same problems as LIKE.
Basically:
SELECT * from syscomments where CHARINDEX( [text], '*') > 0.
Easiest workaround I'm aware of.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 29, 2012 at 3:30 pm
Hi Craig - What does the > 0 do in your query? I looked on MSDN to find out what CHARINDEX does. But I haven't seen anything like that in the examples.
June 29, 2012 at 3:31 pm
GP Van Eron (6/29/2012)
Hi there,Had interesting task today, to seach sps / views in a db for any containing '*' character.
So far I have tried the following
SELECT OBJECT_NAME(id)
FROM syscomments
SELECT OBJECT_NAME(id) , text
FROM syscomments
WHERE [text] LIKE '%!*%' ESCAPE '!'
GROUP BY OBJECT_NAME(id)
But it still just returns all objects, that definitely do not contain '*' I was hoping the ESCAPE would do the trick, but no luck. Anyone have any suggestions?
Thank you kindly!
GP
Your code seems to work OK in the various AdventureWorks databases.
You really don't need the ESCAPE, because * is not a wildcard in a like clause, but it should still work OK.
This should also work OK:
WHERE [text] LIKE '%*%'
June 29, 2012 at 3:31 pm
DataAnalyst110 (6/29/2012)
Hi Craig - What does the > 0 do in your query? I looked on MSDN to find out what CHARINDEX does. But I haven't seen anything like that in the examples.
Checks that it exists. CHARINDEX returns the location in the string that the value was found at. If it's 0, it means the character was unfound. Any value greater than 0 means it found something. Since we don't care WHERE it was, just checking for >0 is enough.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 29, 2012 at 3:33 pm
I may have just realized the problem. Thanks for the reminder Michael, I went off escaping and didn't really think through the allowable characters for the pattern replacements.
Please remember that comments can also be built as:
/* */ as well as --.
What you may want to search for is %SELECT *%... assuming that's what you're trying to find.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 29, 2012 at 3:35 pm
Another thin I would change is the where you are doing the search. syscomments if for backward compatiblity for SQL Server 2000. Instead of syscomments you should be using sys.sql_modules.
June 29, 2012 at 3:37 pm
Evil Kraig F (6/29/2012)
I may have just realized the problem. Thanks for the reminder Michael, I went off escaping and didn't really think through the allowable characters for the pattern replacements.Please remember that comments can also be built as:
/* */ as well as --.
What you may want to search for is %SELECT *%... assuming that's what you're trying to find.
What you may want to search for is %SELECT%*%... assuming that's what you're trying to find. What if the * is on the next line?
June 29, 2012 at 3:47 pm
DataAnalyst110 (6/29/2012)
Hi Craig - What does the > 0 do in your query? I looked on MSDN to find out what CHARINDEX does. But I haven't seen anything like that in the examples.
Makes perfect sense, thanks!
June 29, 2012 at 3:55 pm
Thank you everyone for your quick and generous replies! Much appreciated!
June 29, 2012 at 4:51 pm
Lynn Pettis (6/29/2012)
Evil Kraig F (6/29/2012)
I may have just realized the problem. Thanks for the reminder Michael, I went off escaping and didn't really think through the allowable characters for the pattern replacements.Please remember that comments can also be built as:
/* */ as well as --.
What you may want to search for is %SELECT *%... assuming that's what you're trying to find.
What you may want to search for is %SELECT%*%... assuming that's what you're trying to find. What if the * is on the next line?
Cause you'll end up finding things like this: SELECT colA, colB, colC /* Hey, Lookit me! */ FROM table where...
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 29, 2012 at 5:33 pm
Evil Kraig F (6/29/2012)
Lynn Pettis (6/29/2012)
Evil Kraig F (6/29/2012)
I may have just realized the problem. Thanks for the reminder Michael, I went off escaping and didn't really think through the allowable characters for the pattern replacements.Please remember that comments can also be built as:
/* */ as well as --.
What you may want to search for is %SELECT *%... assuming that's what you're trying to find.
What you may want to search for is %SELECT%*%... assuming that's what you're trying to find. What if the * is on the next line?
Cause you'll end up finding things like this: SELECT colA, colB, colC /* Hey, Lookit me! */ FROM table where...
Yeppers. And yours will miss this:
select
*
from
mytable
where
...
Looks like you miss things you shouldn't or find things you shouldn't. Just means you can't totally automate the changes you are trying to make.
I should know, I had to do this where I am working now.
June 29, 2012 at 5:58 pm
Lynn Pettis (6/29/2012)
Looks like you miss things you shouldn't or find things you shouldn't. Just means you can't totally automate the changes you are trying to make.I should know, I had to do this where I am working now.
Agreed, there's no good way out of the issue except for a by-hand review of every proc that has a * in it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 30, 2012 at 4:38 pm
Lynn Pettis (6/29/2012)
Evil Kraig F (6/29/2012)
I may have just realized the problem. Thanks for the reminder Michael, I went off escaping and didn't really think through the allowable characters for the pattern replacements.Please remember that comments can also be built as:
/* */ as well as --.
What you may want to search for is %SELECT *%... assuming that's what you're trying to find.
What you may want to search for is %SELECT%*%... assuming that's what you're trying to find. What if the * is on the next line?
Careful, folks... that wil also find SELECT SomeColA * SomeColB and a whole host of other things that you might not expect.
--Jeff Moden
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply