Search code examples
sql-serversql-server-2017

MSSQL - Find and Replace text in ALL stored Procedures

I recently renamed a table in my database. As the result, I need to find and replace the table name in all of the stored procedures in my database.

SELECT DISTINCT
       o.name AS Object_Name,
       o.type_desc
  FROM sys.sql_modules m
       INNER JOIN
       sys.objects o
         ON m.object_id = o.object_id
 WHERE m.definition Like '%myoldtable%'
 order by OBJECT_NAME desc

When I run the script above, I get 230 matches. There are 230 stored procedures in my database that somehow reference that table name. I want to do a massive find and replace from myoldtable to mynewtable. Is that possible?

Solution

  • I'd suggest using an SSDT database project in Visual Studio (or another database management tool) - it's fairly simple to import the database objects and refactor the table name, then create a change script to apply to your database(s). You can then use this project to version control changes to your DB and even integrate with CI/CD. Here are some links to get you started:

    https://learn.microsoft.com/en-us/sql/ssdt/import-into-a-database-project?view=sql-server-2017

    https://learn.microsoft.com/en-us/sql/ssdt/how-to-use-rename-and-refactoring-to-make-changes-to-your-database-objects?view=sql-server-2017