Looking For a Table or Stored Procedure in a Database

While I've designed a lot of databases, I've maintained even more. Often databases have hundreds of tables and thousands of stored procedures. Some company's have good naming conventions and others get a glazed look when you mention naming conventions. When trying to find a stored procedure that no one knows the name of, but know it has a variable called @UserName in it, can be quite a daunting task. It be nice if there was a way to query the text of the stored procedure for @UserName (we are using a database after all). Well you can. Here is the code:

Template:

SELECT sc.[id], sc.[text], so.[name]
FROM dbo.syscomments sc
INNER JOIN dbo.sysobjects so
ON sc.[id] = so.[id]
WHERE [Text] LIKE '%query text%'
ORDER BY so.[Name]

Example:

SELECT sc.[id], sc.[text], so.[name]
FROM dbo.syscomments sc
INNER JOIN dbo.sysobjects so
ON sc.[id] = so.[id]
WHERE [Text] LIKE '%@UserName%'
ORDER BY so.[Name]