How to find a Query in all stored procedure
-
Hi,
I have database with over 100 SP. Now i have a query which i am suppose to search in all SP. I cannot do it manually because it is a tedious job. I would like to have a help on it.
-
SELECT name
FROM sys.objects
WHERE type='P' AND OBJECT_DEFINITION(object_id) LIKE '%myquery here%'
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
you can also use the sys.sql_modules view.
-
Mark-101232 (9/5/2012)
SELECT name
FROM sys.objects
WHERE type='P' AND OBJECT_DEFINITION(object_id) LIKE '%myquery here%'
Your solution works only when every thing exact is pasted in myquery area in the above query. By the word exact i mean that i have to also specify equal number of spaces. To tell you guys what i mean to say , is suppose if the query is
select * from table1 where id=5and if i specify as
select * from table1 where id=5it is not working.Could anybody help with the above. Please
-
Robert klimes (9/5/2012)
you can also use the sys.sql_modules view.With sql_modules i found this query but it also as the same problem as specified above
SELECT o.nameFROM sys.sql_modules m
JOIN sys.objects o ON m.object_id = o.object_id
WHERE definition like
'%QueryHere%'
-
Have you tried to use a method to eliminate duplicate spaces?
Here's one way to do it http://www.sqlservercentral.com/Forums/FindPost821209.aspx
You might need to replace char(9), char(10) and char(13) as well.
Basically something like this (It might be slow but functional. However, I let you the task of testing it.):
SELECT o.name
FROM sys.sql_modules m
JOIN sys.objects o ON m.object_id = o.object_id
WHERE replace(replace(replace(replace(replace(replace(replace(ltrim(rtrim(replace(replace(replace(definition,CHAR(9),' '),CHAR(10),' '),CHAR(13), ' '))),
' ',' '),
' ',' '),
' ',' '),
' ',' '),
' ',' '),
' ',' '),
' ',' ')
like
'%QueryHere%'
-
Shadab Shah (9/5/2012)
Mark-101232 (9/5/2012)
SELECT name
FROM sys.objects
WHERE type='P' AND OBJECT_DEFINITION(object_id) LIKE '%myquery here%'
Your solution works only when every thing exact is pasted in myquery area in the above query. By the word exact i mean that i have to also specify equal number of spaces. To tell you guys what i mean to say , is suppose if the query is
select * from table1 where id=5and if i specify as
select * from table1 where id=5it is not working.Could anybody help with the above. Please
Try removing spaces from both sides of the LIKE
SELECT nameFROM sys.objects
WHERE type='P' AND REPLACE(OBJECT_DEFINITION(object_id),' ','') LIKE REPLACE('%myquery here%',' ','')
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
What about if his query in SP is like that:
select *
from table1 -- some comment
--apply filters
where id=5
I guess, he will get some problems in this case.
There is nothing he can really which can help him at 100% guarantee.
I would just try to search simply for table (any relevant object) name, then review manually stored procs which do reference it.
-
you could replace all spaces with wildcards
SELECT o.nameFROM sys.sql_modules m
JOIN sys.objects o ON m.object_id = o.object_id
where definition like replace('%select * from table1 where id=5%',' ','%')
-
This should do the trick (building on Robert's query and Luis's suggestion) :
-- pass the query text as search string variableDECLARE @query varchar(1000)
SET @query =' INSERT
INTO @dbInfo_prep '
-- string cleanup
SET @query=REPLACE(@query,CHAR(9),' ')
SET @query=REPLACE(@query,CHAR(13),' ')
SET @query=REPLACE(@query,CHAR(10),' ')
BEGIN
WHILE CHARINDEX(' ',@query) > 0
SET @query=REPLACE(@query,' ',' ')
END
SET @query='%'+LTRIM(RTRIM(@query))+'%'
-- Output
SELECT o.name
FROM sys.sql_modules m
JOIN sys.objects o ON m.object_id = o.object_id
where definition like @query
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
I understand what you did Alan, but I believe that the problem is on the definition column, not the string used.
-
Searching the database for a query?? You mean not everyone uses VSS or TFS or other repository?
Personally, I use notepad++ for doing RegEx searches against the latest source code on my local disk. The program lets you click on each FIND to expand it and see if it is what you were looking for.
On well-documented code, one can search comments instead of code. I guess it comes down to whoever writes using patterns, may search using patterns.
-
Bill Talada (9/5/2012)
Searching the database for a query?? You mean not everyone uses VSS or TFS or other repository?Was that sarcasm? I really hope so, otherwise you might not have been living in the real world. However, I would love to live in that world.
Bill Talada (9/5/2012)
Personally, I use notepad++ for doing RegEx searches against the latest source code on my local disk. The program lets you click on each FIND to expand it and see if it is what you were looking for.That means you need to have all the source code on your local disk? That's not optimal on large projects.
Bill Talada (9/5/2012)
On well-documented code, one can search comments instead of code. I guess it comes down to whoever writes using patterns, may search using patterns.That's too difficult to happen on large development teams. Even with standards, every person has a different style to write.
-
I can not see any points of trying to search for a specific query which can be implemented in unlimited number of ways. Eg. comments, white spaces, multi-lines, different table aliases at the end.
Again, I would advise to simply search for objects interested and then do manual review of code.
-
Luis Cazares (9/5/2012)
Bill Talada (9/5/2012)
Searching the database for a query?? You mean not everyone uses VSS or TFS or other repository?Was that sarcasm? I really hope so, otherwise you might not have been living in the real world. However, I would love to live in that world.
If you have poor development process it doesn't mean that everyone should.
Alex Suprun
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply