July 20, 2015 at 9:46 pm
I need to search for such SPs in my database in which the queries for update a table contains where clause which uses non primary key while updating rows in table.
If employee table have empId as primary key and an Update query is using empName in where clause to update employee record then such SP should be listed. so there would be hundreds of tables with their primary key and thousands of SPs in a database. How can I find them where the "where" clause is using some other column than its primary key.
If there is any other hint or query to identify such queries that lock tables would be additional help for my case, I only found the above few queries that are not using primary key in where clause.
Shamshad Ali
July 27, 2015 at 1:13 am
Not exactly the same thing, but Jonathan Kehayias showed how to query the Plan Cache for queries that use a specific index:
It can be a good start..
July 27, 2015 at 7:49 am
shamshad.ali (7/20/2015)
I need to search for such SPs in my database in which the queries for update a table contains where clause which uses non primary key while updating rows in table.If employee table have empId as primary key and an Update query is using empName in where clause to update employee record then such SP should be listed. so there would be hundreds of tables with their primary key and thousands of SPs in a database. How can I find them where the "where" clause is using some other column than its primary key.
If there is any other hint or query to identify such queries that lock tables would be additional help for my case, I only found the above few queries that are not using primary key in where clause.
Shamshad Ali
I don't think there is any tool that is going to be able to do this. Certainly no tool that can do it with 100% accuracy. You are going to have to find all your procedures that do an update first. Something like RedGate's sql search would be a great tool to help find the list of procedures. From there you have to look at the code by hand and determine if the update is using the primary key of the table it is updating. Use caution here of course, just because an update isn't referencing the primary key in the where predicates does NOT mean the query has a major problem. Your simplified example is a good chance of being an issue but there are other cases where using the PK would actually be a bad thing. What you need to look for is that each of your updates has a covering index for the predicates involved.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply