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).

We shall speak of this naming standard no more.

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):

Serving suggestion, not actual product.

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”.

Wouldn’t it be awesome if the “ALTER” option wasn’t greyed out here?

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.

No fear.

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”.

One thought on “SSMS: Search and replace across multiple objects in a database

  1. Pingback: Find and Replace Database Objects in SSMS – Curated SQL

Let me hear your thoughts!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.