June 27, 2008 at 9:26 am
I am looking to see if anyone knows how to find if the tables referenced in the Procedure and or View contains a table hint.
I have been unsuccessful to discover any system tables or RegEx that may do this.
I'm looking to find out how to scan SQL files for table hints without physically reviewing them.
Any thoughts?
June 27, 2008 at 12:17 pm
Well, off the top of my head, you could do something like:
select *
from sys.all_objects
inner join sys.sql_modules
on all_objects.object_id = sql_modules.object_id
where definition like '%nolock%' -- Put the hint(s) you're looking for here
Would something like that do what you need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 27, 2008 at 12:33 pm
It's very close but I want to tell if the procedure inside of the definition has x tables joined together and need to know how many of those tables have table hints.
June 27, 2008 at 12:40 pm
I don't know a way to find that automatically.
You can add the table names to your Where clause, and narrow down the search. But so far as I know, you'll still have to look at the code to find what you're looking for.
Possibly, one of the automatic documentation applications could do what you need. Check out RedGate and ApexSQL for such applications.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply