Credit goes to the Cincinnati SQL Server User’s Group/Phil Japikse for the following tip. Two things of interest in this post. Ability to create Sql Templates within SQL Server Management Studio and SQL to find objects named like ‘%here%’ across all objects in a database.
USE <databasename,string,@p1>
GO
SELECT OBJECT_NAME(OBJECT_ID)
FROM sys.sql_modules
WHERE definition like '%<string_to_find,string,@p2>%'
Now that you have the SQL it is possible to create a SQL Template (View – SQL Templates) whereby you can simply select and push Ctrl-Shift-M for variable substitution. Within the Template Window create a new folder to store your custom templates, create a new template, paste the above SQL and Save. To use simply click on the template and Ctrl-Shift-M to be prompted for the variables and values.
Great stuff. Thanks guys for the tip.