SQLTeam.com | Weblogs | Forums

Pulling Where clauses of SQL objects

sql2012

#1

Hi,

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.


#2

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%'