SQLTeam.com | Weblogs | Forums

Pulling Where clauses of SQL objects




I am not sure if this can be done or not. I am not sure if I am even going to use the correct language for saying this, so I apologize in advance.

I have about 20 Store procedures (I don't feel like opening each one up to see what the where clause logic is). I was wondering if there was a way for me to pull a query that tells me what the where clauses are in those Store Procedures.


Don't think you can get to "just the WHERE clause" per se, but you can get the source code of all the desired SProcs using something like:

SELECT	M.definition
FROM	sys.objects AS O
		JOIN sys.sql_modules AS M
			 ON M.object_id = O.object_id
WHERE	O.type = 'P'
	AND O.name IN ('MySProc1', 'MySProc2')

One snag you might have is if the [definition] is truncated. Depending on what SQL tool you are using (and how it is configured) then all columns may be truncated at 255, or 8,000 characters. If that's a problem for you then you would have to find a way to work around that [e.g. check the CONFIG Max Column Length] (or look at each one in turn of course).

If you are looking for "something in particular" in the WHERE clause then you could add

	AND M.definition LIKE '%SomeUniqueSnippet%'