Dynamic SQL references will not be handled, so this feature does not obviate the need for solid test plans. Thankfully, we can use SQL Prompt's Smart Rename feature, which will automatically modify almost all references to the renamed object, in the current database. This diagram hints at the magnitude of the task ahead of you if you need to make name changes manually.
Figure 1 shows the many objects that reference it.įigure 1: Dependency diagram for Purchasing.PurchaseOrders For example, in the SSMS Object Explorer, right-click on Purchasing.PurchaseOrders in the WideWorldImporters database, and choose " View Dependency Diagram for.
SQL Server has tools to help you discover dependencies, like the sys.sql_expression_dependencies catalog view (which I made use of in this post to find three- and four-part names), or you can use the object dependency viewer in SSMS, simply by right-clicking an object and choosing View Dependencies, although the UI is a bit lean on details.Īlternatively, Redgate's SQL Dependency Tracker tool integrates with SSMS and provides a detailed dependency diagram for any selected object. Making these changes by hand may, reasonably, only take a few hours, but who has a few hours?
#Sql toolbelt vs prompt code
Throughout all the code and constraints, you must make sure you know about all the possible side-effects of one seemingly-simple change. Smart RenameĬhanging the name of a code object, table or column can be a laborious, even daunting task, because of all the dependencies that can exist in your database. A less frequent but harder requirement is to change the 'public interface' of an object, such as by changing the name of an object, or column, or even by splitting tables to achieve a better design. My previous article focused on small scale code refactoring, limited to altering the 'internals' of a single batch or object.
You hope not to have to use them too often, but when you do, they are extremely valuable. The refactoring tools in SQL Prompt are more like the ones in the snakebite kit that you take on a long hike in the desert. Many of the tools that SQL Prompt provides are ones that you'll use more or less every single day you write T-SQL code. Louis Davidson demonstrates how SQL Prompt can significantly lessen the pain involved in occasional, 'heavyweight' database refactoring processes, such renaming modules, tables and columns (Smart Rename), or splitting tables (Split Table).