September 5, 2012 at 2:36 pm
Alexander Suprun (9/5/2012)
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.
But it do means that it's possible.
I could have taken this as a sarcastic comment, meaning that it's a good way to work (a.k.a. best practice), but there are many companies that are not willing to invest (money/time/effort) on it. That's an ugly truth.
September 5, 2012 at 3:00 pm
Eugene Elutin (9/5/2012)
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.
The title of this post is How to find a query in all stored prodedures and this kind of requirement is common. My earlier code addresses white space, multi-lines, comments, etc. Yes, there is are many tools in the GUI as well as third party tools but they are not always available.
Again, I would advise to simply search for objects interested and then do manual review of code.
That sounds painful, especially when you have hundreds of stored procs with tons of code; and especially when you can write a query (and modify as needed) that does this for you.
-- Itzik Ben-Gan 2001
September 5, 2012 at 11:07 pm
Mark-101232 (9/5/2012)
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%',' ','')
Hi Mark,
I tried your solution but it is not working. When i specify a single word like say 'table1' it shows the name of SP in which table1 is present but fails when i specify the query. Definetly there is space problem. Still not able to figure out the solution
September 5, 2012 at 11:24 pm
Luis Cazares (9/5/2012)
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%'
I Don't know that is the problem but this solution is not working
September 5, 2012 at 11:26 pm
Robert klimes (9/5/2012)
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%',' ','%')
Thaks for the solution but this solution works only for basic queries .When the queries have a function like datediff or any other like rank it fails 🙁 to find the SP
September 5, 2012 at 11:28 pm
XMLSQLNinja (9/5/2012)
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
Hi Alan,
I tried your solution also but 🙁 your solution is also not working.I tried to find out what was wrong with your query but as far as i think logically it is write but still cannot figure it out why it is not showing the names of SP
September 5, 2012 at 11:30 pm
Eugene Elutin (9/5/2012)
Again, I would advise to simply search for objects interested and then do manual review of code.
Hi,
I think that would be next to impossible 🙂 .
September 6, 2012 at 4:59 am
Shadab Shah (9/5/2012)
Eugene Elutin (9/5/2012)
Again, I would advise to simply search for objects interested and then do manual review of code.Hi,
I think that would be next to impossible 🙂 .
No, that is not impossible, it's very much possible, but just takes longer.
More than that, it's much more possible than to automate "How to find a Query in all stored procedure" with 100% accurate results for all possible cases.
It would be a bit more possible to automate this if all your queries would be written following some very strict standards without any deviations from the standard. Otherwise - no chance - you will never get 100% accuracy. Period.
SELECT * FROM Table1
OR
SELECT *
FROM dbo.Table1
OR
SELECT Col1, Col2, Col3 ... ColN
FROM Table1 --select all columns of table 1)
OR
SELECT T1.*
FROM dbo.Table1 AS T1
WHERE 1=1
OR
SELECT t.* FROM (SELECT 1 a) a CROSS JOIN Table1 t
All above queries will produce exactly the same result, what kind of search string would you try to find them all?
And that is for very simple query. What about one with JOIN's, sub-queries, CTE's etc.
Good Luck on it!
September 6, 2012 at 7:09 am
Eugene is right, there's no way to have accurate results.
This are meant to be tools to help, not to automate a process.
I was going to suggest you to generate a script of all your stored procedures and use the search tool from any text editor (including SSMS). However, this will only give you the code at a point in time and you'll have to recreate it every time.
If you feel that having 100 SPs is a large database, tell me when you're arriving to the 1000 plus the same amount of functions and SQL code in the front end.
This far, you have just tested our solutions but you haven't shown any effort on trying to improve them or even understand them.
My last suggestion is to make a function to clean up your queries. It will take a large amount of time but it will be more accurate.
1. REPLACE CHAR(13)+CHAR(10), CHAR(10)+CHAR(13) and CHAR(13) with CHAR(10). To have a unique line break.
2. Remove comments (my suggestion is to use STUFF combined with CHARINDEX to find "--", CHAR(10), "/*", "*/").
3. REPLACE CHAR(10) with SPACE(1).
4. REPLACE multiple spaces with single space (I've shown you were to find the best way to do it).
That should clean up most things. If I'm forgetting something please tell me.
September 6, 2012 at 7:26 am
...
My last suggestion is to make a function to clean up your queries. It will take a large amount of time but it will be more accurate.
1. REPLACE CHAR(13)+CHAR(10), CHAR(10)+CHAR(13) and CHAR(13) with CHAR(10). To have a unique line break.
2. Remove comments (my suggestion is to use STUFF combined with CHARINDEX to find "--", CHAR(10), "/*", "*/").
3. REPLACE CHAR(10) with SPACE(1).
4. REPLACE multiple spaces with single space (I've shown you were to find the best way to do it).
That should clean up most things. If I'm forgetting something please tell me.
The all above will work only for simple queries without JOIN's.
What about table aliases? a.) they can be different for the same table from query to query; b.) sometimes AS word will be used, some times not.
Name qualifiers (database, schema name)?
What about if one query does:
SELECT Col1, Col2 FROM Table1
and another one:
SELECT Col2, Col1 FROM Table1
OR
INSERT INTO Table1 (..) VALUES (..) and INSERT Table1 (..) SELECT ....
An many, many, many more!
September 6, 2012 at 7:33 am
I would say that those are different queries.
It's not the same thing to have
SELECT col1 FROM TableA
or
SELECT a.col1 AS mycol FROM TableA a
Even comments would make a query different, but I just suggested to eliminate them.
However, I still agree with you that there won't be a perfect solution, just better tools.
September 6, 2012 at 7:39 am
Luis Cazares (9/6/2012)
I would say that those are different queries.It's not the same thing to have
SELECT col1 FROM TableA
or
SELECT a.col1 AS mycol FROM TableA a
Even comments would make a query different, but I just suggested to eliminate them.
However, I still agree with you that there won't be a perfect solution, just better tools.
Agree, you may call these two queries different as they produce "different" resultset - different name of the column!
But what about these two:
SELECT t.col1 FROM TableA t
or
SELECT ta.col1 AS col1 FROM dbo.TableA AS ta
?
:w00t:
September 6, 2012 at 7:43 am
IMO, different queries, same result. Just like:
SELECT CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) AS one
and
SELECT 1 one
September 6, 2012 at 7:54 am
Luis Cazares (9/6/2012)
IMO, different queries, same result. Just like:SELECT CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) AS one
and
SELECT 1 one
Very arguable! And not very good example.
In my case queries are exactly the same and not only because they produce the same resutset - they do exactly the same thing and will compile into exactly the same execution plan - the difference is in use of T-SQL syntax.
In your case two queries do very different things. The first one does a lot of conversions and string manipulations, the second does nothing.
Let ask OP what he would think, should his search for query "SELECT Col1 FROM Table1" find my two examples? Will he consider them as "the same queries" or not?
September 6, 2012 at 9:46 am
Shadab Shah (9/5/2012)
Hi Alan,
I tried your solution also but 🙁 your solution is also not working.I tried to find out what was wrong with your query but as far as i think logically it is write but still cannot figure it out why it is not showing the names of SP
This thread has grown a bit :w00t: I am stumped on why that is not working. I am going to try a few things and check back later today.
-- Itzik Ben-Gan 2001
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply