SSMS: Search and replace across multiple objects in a database

Here’s a quick tip that touches on one of the powerful SSMS tricks in my “Management Studio Level-Up” presentation. Say you have a potentially large number of database objects (procedures, functions, views, what have you), and you need to make a search-and-replace kind of change to all of those objects.

You could, of course, put the database in source control and use a proper IDE to replace everything, then check your code back into source control and commit it to the database. That’s obviously the grown-up solution. Thanks for reading this post.

But let’s say for the sake of argument that you haven’t put your database in version control. What’s the lazy option here?

Identify all of the objects

First off, we need to actually identify which objects we need to work on. There’s a DMV in SQL Server for everything, and this is no exception.

SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+OBJECT_NAME([object_id])
FROM sys.sql_modules
WHERE [definition] LIKE '%, description, %'
ORDER BY OBJECT_SCHEMA_NAME([object_id]), OBJECT_NAME([object_id]);

The sys.sql_modules contains the source code of all “modules” – basically all T-SQL-based packaged code in the database (procedures, views, functions, not stuff like tables, users and constraints).

Now that we’ve got a list of objects, let’s script all those objects into a single window.

Open up the object explorer so you can see all your objects (in my case, they’re all views):

Select the “Views” container, then click F7 to bring up the “Object Explorer Details” window. In this window, you can now multi-select objects by holding down Ctrl and clicking all the objects you need.

Now for the magic. Right-click the selection, “Script As”, and “To New Query Editor Window”.

Doing this will create a single query with all of the selected objects!

“DROP and CREATE” or “ALTER”?

Remember that when you DROP and CREATE an object, you risk doing terrible things to other objects that may depend on the ones you’re modifying. But even worse, you will lose any object-level permissions on the dropped object.

As a rule of thumb, always ALTER an object instead of dropping and recreating it whenever you can.

Do the switch

Now all you have to do is replace to your heart’s contents! Do make sure the dropdown says “Current Document” to avoid having an awkward one-on-one meeting with your manager.

Remember that there are two replacements you need to perform:

• Change “CREATE object-type” to “ALTER object-type” on all objects.
• The original replace operation that you wanted to perform.

When you’ve reviewed all your changes and are happy with them, make sure you have a recent backup of the database and hit “Execute”.