Manipulating Multiple Objects in SQL Server Management Studio

By: Ken Simmons   |   Updated: 2009-12-30   |   Comments (1)   |   Related: > SQL Server Management Studio

Problem

Every now and then, I see someone doing repetitive tasks in SQL Server Management Studio such as dropping a stored procedure one by one when they need to remove ten or scripting out a single object at a time because they can't select multiple objects from the Object Explorer. I have even seen people create elaborate scripts to perform tasks that involved manipulating multiple database objects to avoid these mundane tasks. In this tip I will show you how this can be done simply by using SQL Server Management Studio.

Solution

It is very easy to manipulate multiple objects at once in SQL Server Management Studio using the Object Explorer Details window. You can display the Object Explorer Details window by selecting it from the View menu or simply by pressing the F7 shortcut key. Once the Object Explorer Details window is displayed, you can select a folder from the Object Explorer to display the contents in the Object Explorer Details window. Then you can select and manipulate multiple objects as shown in the following image. You can choose to script the DROP and CREATE statements or simply drop all of the objects then and there by selecting Delete from the context menu.

You can even customize the Object Explorer Details window by adding and removing the columns you wish to see. To add and remove columns from the Object Explorer Details window, right-click an existing column and check or uncheck columns from the context menu as shown in the following image.

The Object Explorer Details window gets even more interesting when you are working with Policy-Based Management because not only can you highlight and evaluate multiple policies, you can sort by the policy categories you would like to evaluate. As you can see by the following image, it is easy to select and evaluate all the policies in a category such as Microsoft Best Practices: Maintenance.

As you can see, if you need to generate a script for multiple objects or simply remove multiple objects, the Object Explorer Details window provides a quick and easy interface to perform those tasks. In addition, the Object Explorer Details window also provides other useful features such as sorting capabilities that can make manipulating multiple objects even easier.

Next Steps

I am sure you are familiar with managing database objects, but we slightly touched on Policy-Based Management, which is a new feature in SQL Server 2008. You can find out more about Policy-Based Management and the benefits it provides by reviewing the following links.

• Review tips on Policy Based Management.
• Using Policy Based Management in SQL Server 2008
• Evaluate and Implement Policies in Multiple SQL Server Instances
• Review Policy Based Management in Books Online.
• http://msdn.microsoft.com/en-us/library/bb510667.aspx
• Review using Policy Based Management with Central Management Servers to ease administration across database servers.
• http://msdn.microsoft.com/en-us/library/bb895144.aspx

About the author

Ken Simmons is a database administrator, developer, SQL Server book author and Microsoft SQL Server MVP.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips