Shulou(Shulou.com)06/01 Report--

SQL Prompt automatically retrieves according to the object name, syntax and code snippet of the database to provide users with appropriate code choices. Automatic script settings make the code easy to read-especially useful when developers are not familiar with scripts. SQL Prompt can be installed and used, which can greatly improve the coding efficiency. In addition, users can customize it as needed to make it work in the desired way. This tutorial demonstrates how SQL Prompt can significantly reduce the pain associated with the occasional "heavyweight" database refactoring process, such as renaming modules, tables and columns (smart renaming) or splitting tables (split tables).

Many of the tools provided by SQL Prompt are tools that you use more or less when writing T-SQL code every day. The refactoring tools in SQL Prompt are more like the tools in the snakebite toolkit you use when hiking for a long time in the desert. You don't want to use them often, but when you use them, they will be very valuable. A less common but difficult requirement is to change the "common interface" of an object, such as by changing the name of an object or column, or even by splitting a table to achieve better design.

Intelligent renaming

After you select an object in SSMS object Explorer, SQL Prompt's Smart rename wizard generates a script to rename the object and modify the object that references the renamed object. Modifications are made in the correct order to maintain the integrity of the database.

Because there may be all dependencies in the database, changing the name of a code object, table, or column can be a laborious or even daunting task. In all code and constraints, you must make sure that you understand all the possible side effects of a seemingly simple change. Reasonably, it may only take a few hours to make these changes manually, but who has hours?

SQL Server provides tools to help you discover dependencies, such as the sys.sql_expression_dependencies catalog view, or you can use the object dependency viewer in SSMS by right-clicking the object and selecting "View Dependencies", although UI is somewhat dependent on details.

In addition, Redgate's SQL Dependency Tracker tool integrates with SSMS and provides a detailed dependency graph for any selected object. For example, in SSMS object Explorer, right-click Purchasing.PurchaseOrders, and in the WideWorldImporters database, select View dependency Diagram [objects]. Figure 1 shows many of the objects that reference it.

Figure 1

If you need to change the name manually, this figure shows the difficulty of the task you want to accomplish. Fortunately, we can use SQL Prompt's intelligent renaming feature, which automatically modifies almost all references to renamed objects in the current database. Dynamic SQL references will not be processed, so this feature does not eliminate the need for reliable test plans.

We will start with the simplest database refactoring task, rename the code module, then gradually increase complexity and risk, rename the table, and finally rename the column.

Rename code object

Suppose you write a new stored procedure that Purchasing.PurchaseOrder$ListFinalized calls an existing stored procedure Purchasing.PurchaseOrder$List to get the result set that contains only the final order.

CREATE PROCEDURE Purchasing.PurchaseOrder$List (@ IsOrderFinalized bit) AS BEGIN SELECT PurchaseOrders.PurchaseOrderID, PurchaseOrders.OrderDate, PurchaseOrders.IsOrderFinalized FROM Purchasing.PurchaseOrders WHERE IsOrderFinalized = @ IsOrderFinalized; END; GO CREATE PROCEDURE Purchasing.PurchaseOrder$ListFinalized AS BEGIN EXEC Purchasing. [PurchaseOrder$List] @ IsOrderFinalized = 1; END

Listing 1

Now you decide that you need to change the name of the existing Purchasing.PurchaseOrder$List process to PurchaseOrder$ListAll to clarify that it will return all purchase orders, whether they have been completed or not.

Select in object Explorer: if you have opened the server in object Explorer, you can right-click the name in the query window, and then select Select in object Explorer. If the list has not been refreshed since the object was created, it may only bring you closer to the objects in the list.

After you find the stored procedure in SSMS object Explorer, you can rename it by pressing F2 or right-clicking and selecting Rename, but all you have to do is rename the object, so any existing code that still references the object by its old name will rename it and will now fail.

Message 2812, level 16, status 62, process purchase. PurchaseOrder$ ListFinalized, line 4

The stored procedure "Purchasing.PurchaseOrder $List" could not be found.

Instead, we will use SQL Prompt's smart renaming feature. Purchasing.PurchaseOrder$List right-click in object Explorer and select Smart rename. Change the name to PurchaseOrder$ListAll in the dialog box, as shown in figure 2.

Figure 2

Click next, and you will see a list of tasks that SQL Prompt will perform to rename the object and adjust all related objects that reference the object by name.

Drop the program [Purchasing]. [PurchaseOrder$List]

Set up the program [Purchasing]. [PurchaseOrder$ListAll]

Change program [Purchasing]. [PurchaseOrder$ListFinalized]

Execute the generated script and SQL Prompt will make changes. If there is an error, the script will fail and all changes will be rolled back.

Rename table

Although it is usually easy to change the name of an encoding module, changing the names of tables and columns requires more attention, and you need to carefully examine the generated script so that you know exactly what it is doing. Sometimes because of the functionality that some objects use in SQL Server, the process cannot modify some objects, so you need to manually intervene and modify the generated script.

Simple table renaming

Suppose that for some strange reason, we want to rename the Purchasing.PurchaseOrders table to Purchasing.ThePurchaseOrders. Right-click the table and select Smart Rename. Change the name to ThePurchaseOrders, and then click next. SQL Prompt lists all the necessary actions to resolve all dependencies (as shown in figure 1).

Figure 3

Click View script to see which script it will execute, including changing our stored procedure, Purchasing.PurchaseOrder$ListAll to reference the new table name.

ALTER PROCEDURE Purchasing. [PurchaseOrder$ListAll] (@ IsOrderFinalized bit) AS BEGIN SELECT ThePurchaseOrders.PurchaseOrderID, ThePurchaseOrders.OrderDate, ThePurchaseOrders.IsOrderFinalized FROM Purchasing.ThePurchaseOrders WHERE IsOrderFinalized = @ IsOrderFinalized; END

Listing 2

Execute the script, and you will see a set of PRINT statements informing you of each change you have made.

Limitations of intelligent renaming

For most tables, "smart renaming" is actually magical, but there are some limitations that we need to prove. Fortunately, WideWorldImporters provides us with tables that need to be changed, such as Application.Cities, access with table binding, time extension, and row-level security, all of which we will need to process manually.

Suppose we want to rename the Application.Cities table. Again, just right-click the table and select Smart Rename. However, because the dependent object references the object that we recommend to change, you will now see a longer list of operations.

Figure 4

If you try to execute the script, it will fail. The first error is caused by an attempt to rename Cities to TheCities, as follows. The generated script will use IF @ @ ERROR 0 SET NOEXEC ON, so the next steps will not run, resulting in further redundant errors, which are not shown here.

Message 15336, level 16, status 1, procedure sp_rename, line 565

Cannot rename object'[Application]. [Cities] 'because it participates in a mandatory dependency.

This illustrates the limitations of the intelligent renaming feature. The generated script uses only calls to sp_rename stored procedures, but this does not apply to every table. For example, this operation is not supported here on timesheets, such as Application.Cities, so it will not work.

To avoid this error, you need a block of code to re-encode the code to modify the Application.Cities table to close the system version, change the name of the table (or its associated history table, Application.Cities_Archive (History) to keep it clear), and then re-enable system version control.

In this case, however, there is further complexity. The WideWorldImporters database implements row-level security, which is achieved using security policies. One of these strategies, FilterCustomersBySalesTerritoryRole, contains a predicate that references an inline table-valued function (iTVF) Application.DetermineCustomerAccess called the Application.Cities table. This iTVF uses schema binding, which means that we cannot change or delete it while it is still referenced by the security policy, but we need to change it because it references the table that Application.Cities wants to rename.

As you can see, this situation can lead to a large number of manual changes. We will need to change the security policy to remove the predicate that references iTVF so that we can then delete iTVF so that we can disable system versioning and then rename the table. When done, we will need to re-enable system versioning, re-create the iTVF and re-establish an effective security policy.

-- Original code:-- EXEC sp_rename N' [Application]. [Cities]', NintenTheCities` NumberOBJECT'GO-- Replaced with:-- Take off row level security PRINT N'Altering [Application]. [DetermineCustomerAccess]'GO ALTER SECURITY POLICY [Application]. [FilterCustomersBySalesTerritoryRole] DROP FILTER PREDICATE ON [Sales]. [Customers] GO IF @ @ ERROR 0 SET NOEXEC ON GO ALTER SECURITY POLICY [Application]. [FilterCustomersBySalesTerritoryRole] DROP BLOCK PREDICATE ON [Sales]. [Customers] AFTER UPDATE GO IF @ @ ERROR 0 SET NOEXEC ON GO-Deal with the schema bound objects. You could change to-- a blank function and let the later steps ALTER the function-- but we need this to reapply row-level security DROP FUNCTION Application.DetermineCustomerAccess GO IF @ @ ERROR 0 SET NOEXEC ON GO PRINT N'Renaming table, and handling system version table' GO-- Remove system versioning ALTER TABLE Application.Cities SET (SYSTEM_VERSIONING = OFF) GO IF @ @ ERROR 0 SET NOEXEC ON GO-- Now rename the column EXEC sp_rename N' [Application]. [Cities]', Noble TheCities` NumberOBJECTT'GO IF @ @ ERROR 0 SET NOEXEC ON GO EXEC sp_rename N' [Application]. [Cities_Archive]', Noble the Citiestically archived Archiveyards, ERROR OBJECT'IF @ @ ERROR 0 SET NOEXEC ON GO-- turn back on temporal extensions. Rename temporal table if-desired ALTER TABLE Application.TheCities SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = Application.Cities_Archive)) GO IF @ @ ERROR 0 SET NOEXEC ON GO-- Add back the function And manually change the name-- of the Cities table to TheCities CREATE FUNCTION [Application]. [DetermineCustomerAccess] (@ CityID int) RETURNS table WITH SCHEMABINDING AS RETURN (SELECT 1 AS Acce***esult WHERE IS_ROLEMEMBER (Numbdbroomowner') 0 OR IS_ROLEMEMBER ((SELECT sp.SalesTerritory FROM [Application] .TheCities AS C INNER JOIN [Application] .StateProvinces AS sp) ON C.StateProvinceID = sp.StateProvinceID WHERE C.CityID = @ CityID) + N'Sales') 0 OR (ORIGINAL_LOGIN () = Noble Web site 'AND EXISTS (SELECT 1 FROM [Application] .TheCities AS C INNER JOIN [Application] .StateProvinces AS sp) ON C.StateProvinceID = sp.StateProvinceID WHERE C.CityID = @ CityID AND sp.SalesTerritory = SESSION_CONTEXT (named SalesTerritory') GO-- Turn back on row-level security IF @ @ ERROR 0 SET NOEXEC ON GO ALTER SECURITY POLICY [Application]. [FilterCustomersBySalesTerritoryRole] ADD FILTER PREDICATE [Application]. [DetermineCustomerAccess] ([DeliveryCityID]) ON [Sales]. [Customers], ADD BLOCK PREDICATE [Application]. [DetermineCustomerAccess] ([DeliveryCityID]) ON [Sales]. [Customers] AFTER UPDATE; GO IF @ @ ERROR 0 SET NOEXEC ON GO ALTER SECURITY POLICY [Application]. [FilterCustomersBySalesTerritoryRole] WITH (STATE = ON); GO IF @ @ ERROR 0 SET NOEXEC ON GO

Listing 3

Obviously, this is a daunting task, but all the object changes we handle for you, except for schema binding, system versioning, and row-level security. Most of these changes are not the norm for the tables you encounter, but you occasionally need to deal with each case.

Tip: in addition to making database changes (database backups that you should always have when making structural changes, such as renaming objects), it is best to use another SQL Toolbelt tool: SQL Compare. Use it to take a snapshot of the code in the database before making any changes, and then compare the database to the snapshot after the changes are complete. This will allow you to find any changes you didn't expect without using a backup. For example, if you delete a schema-bound object, you may have lost the security of that object. It's also gratifying to see that nothing has changed since the failed deployment, because you don't realize that you have to deal with row-level security first!

However, renaming tables is a relatively safe task for the public interface of the code. The table name usually does not appear in the output of the query, so security changes are made if all access is made through stored procedures or views. But renaming is a completely different story.

Rename the column

Imagine a project going on for two weeks, and you've written a lot of T-SQL-encoded objects, views, triggers, procedures, constraints, and so on, and then suddenly realize that the columns of the Product table are spelled ProductNmber. You need to make changes before publishing. I had lost count of the number of times I finished building a set of tables or new columns before I realized that I had misspelled "hybid" or "soliciation". Of course, while I like SQL Prompt's code completion, it automatically populates the mix as easily as a mix, so you may have to wait until the code review to notice the error.

For example, we will make changes to the columns in OrderDate's newly renamed ThePurchaseOrders table. Our Purchasing.PurchaseOrder$ListAll stored procedure returns the PurchaseUserID,OrderDate and IsOrderFinalized columns. In other words, these three columns are part of the interface.

CREATE PROCEDURE Purchasing.PurchaseOrder$ListAll (@ IsOrderFinalized bit) AS BEGIN SELECT ThePurchaseOrders.PurchaseOrderID, ThePurchaseOrders.OrderDate, ThePurchaseOrders.IsOrderFinalized FROM Purchasing.ThePurchaseOrders WHERE IsOrderFinalized = @ IsOrderFinalized; END

Listing 4

If you want to rename one of these columns in the table, you can use Smart Rename again. Just like the table example, right-click the column in OrderDateSSMS object Explorer and rename it to OrderDate2. The SQL prompt finds all objects that reference this column, including the Purchasing.PurchaseOrder$ListAll procedure, and the generated script updates it accordingly.

SELECT ThePurchaseOrders.PurchaseOrderID, ThePurchaseOrders.OrderDate2, ThePurchaseOrders.IsOrderFinalized FROM Purchasing.ThePurchaseOrders WHERE IsOrderFinalized = @ IsOrderFinalized

Listing 5

However, this means that users of this process will now see OrderDate2 instead of OrderDate. If this is a new development and no one has started using the code, then this is not a real problem, but if you need the user's point of view to remain the same, you need to fix the code. If the original query used an alias, this problem can be easily avoided, as shown in listing 6, because any subsequent changes to the column name now do not affect the public interface.

SELECT ThePurchaseOrders. PurchaseOrderID AS PurchaseOrderID, ThePurchaseOrders.OrderDate AS OrderDate, ThePurchaseOrders.IsOrderFinalized AS IsOrderFinalized FROM Purchasing.ThePurchaseOrders WHERE IsOrderFinalized = @ IsOrderFinalized

Listing 6

The real worry is that unless you use aliases piously, you may end up mixing interface changes with areas where the interface has not changed. Because the actual script used to change the column is displayed, you can easily use find on the script to determine what you want to change.

Split table

By selecting an object in SSMS object Explorer, SQL Prompt's split Table wizard aims to generate a script that creates linked tables, modifies the original table, and modifies any objects that reference the primary table. After checking the script, you can execute it. SQL Prompt wraps all changes in one transaction, so you can roll them back in case any errors occur.

You won't have to split an existing table in two or run the risk of being corrupted, but when you do, SQL Prompt's split Table feature can save a lot of time and effort. The main reason I consider doing this is that for performance reasons, I "isolate" very large columns in existing tables, but sometimes just for convenience.

For example, suppose we want to add system versioning to the Purchasing.ThePurchaseOrders table. We just want to keep the version history in this OrderDate2 column. In fact, even if we want to version most of the contents of the table, we may not want to keep the history on the two nvarchar (max) columns, because each update creates a new copy of the text, which can contain up to 2GB text.

Therefore, our goal is to move the OrderDate2 column out of the ThePurchaseOrders table and into the new table, and then we can apply system versioning to it. Right-click the ThePurchaseOrders table, select split Table, and the wizard will appear. Name the new auxiliary table, such as ThePurchaseOrdersTemporal, then on the next screen, move the copy key PurchaseOrderId, and then move OrderDate2 to the new table, as shown in figure 5. Both tables will have primary keys, so there can be no duplicate rows in both tables.

Figure 5

The next screen will ask you to create a foreign key that determines the order in which data is inserted into these tables.

Figure 6

This ensures the existing value Purchasing.ThePurchaseOrdersTemporal in the PurchaseOrderID column referenced by any row added to the Purchasing.ThePurchaseOrders table.

Click next, and you will see information about the action to be performed, the dependencies of the changes, and all warnings related to the required action. In this case, we will see a warning that it cannot handle non-standard filegroups and there is no guarantee that data will not be automatically retained when columns are deleted from the parent table (although in this case, the generated script will save your data in the new table).

Figure 7

Of course, you always want to review and fine-tune one of these generated scripts. SQL Prompt may not be correct every time. You need to make sure that the changes made by the tool are consistent with the way you plan to use the data. For example, the OrderDate2 column is defined as NOT NULL. However, since the column is in the related table, the column can technically be empty because you cannot force a 1-1 relationship.

Clicking the View script button causes the tool to generate a script that you can use to apply changes. It creates a new table and its primary key, loads data into the newly created table, deletes the column from the original table, changes all related objects, adds FOREIGN KEY, and eventually establishes extended properties on the columns of the new table.

All dependent objects will be changed to resolve the new schema design. For example, the PurchaseOrder $ListAll procedure is modified to replace the reference to Purchasing.ThePurchaseOrders, and INNER JOIN is used between Purchasing.ThePurchaseOrders and Purchasing.ThePurchaseOrdersTemporal, as shown in listing 7.

This is INNER JOIN because both rows are expected to be required because they will be part of the table together. This is true even if you select only columns that allow null values (you may not want this, so check each case individually and change the code accordingly).

ALTER PROCEDURE Purchasing. [PurchaseOrder$ListAll] (@ IsOrderFinalized bit) AS BEGIN SELECT ThePurchaseOrders.PurchaseOrderID, ThePurchaseOrdersTemporal.OrderDate, ThePurchaseOrders.IsOrderFinalized FROM (Purchasing.ThePurchaseOrders INNER JOIN Purchasing.ThePurchaseOrdersTemporal ON ThePurchaseOrders.purchaseorderid= ThePurchaseOrdersTemporal.purchaseorderid) WHERE IsOrderFinalized = @ IsOrderFinalized; END

Listing 7

As with any process that modifies a table and data loss may occur, it is strongly recommended that you check the generated script and test the deployment at least once on a copy of the database structure, and back up if there is something wrong with the script. Once fully satisfied, you can run the script and then apply the time extension to the Purchasing.ThePurchaseOrdersTemporal table instead of the original table.

Finally, remind you to test your code and make sure that all code works as expected in the SQL Server object and user interface. You are greatly changing the interface of the object to the outside world.

Conclusion

In this tutorial, we looked at two of the least used features in SQL Prompt, but they are priceless when you need them. If you have to rename objects or columns, or even split a table into two tables, there is no doubt that intelligent renaming and splitting tables can save you a lot of time, especially if you have implemented SQL Server to use a server-centric paradigm of databases that contain constraints, triggers, and stored procedures.

You can make large-scale name and structure changes with less effort, which means you can devote more time and effort to testing that the application will continue to work as expected after refactoring.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.