Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Manipulating Multiple Objects in SQL Server Management Studio


By:   |   Read Comments (1)   |   Related Tips: More > SQL Server Management Studio


ALERT: Share your SQL Server knowledge and become a MSSQLTips author


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 Update:


signup button

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    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

Learn more about SQL Server tools