mssqltips logo

Manipulating Multiple Objects in SQL Server Management Studio

By:   |   Updated: 2009-12-30   |   Comments (1)   |   Related: More > 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.

object explorer details

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.

microsoft sql server management

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.

object explorer details

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.



Last Updated: 2009-12-30


get scripts

next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Thursday, January 14, 2010 - 12:35:26 PM - jtobikidd Back To Top

Which version of SQL Server Management Studio is illustrated in this article?  I am running version 9.00.4035.00 and the only options I have when I right-click a column name are:

  • New...
  • Filter ->
  • Reports ->
  • Refresh
Thank you


download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools