SQL Server 2012 : SQL Server Management and Development Tools - Object Explorer (part 1)

Object Explorer offers a well-organized view of the world of SQL Server. The top level of the tree lists the connected servers. Object Explorer can connect to a server regardless of whether the server is known by Registered Servers. The server icon color indicates whether the server is running.

1. Navigating the Tree

In keeping with the Explorer metaphor, Object Explorer (see Figure 1) is a hierarchical, expandable view of the objects available within the connected servers.

Figure 1 Object Explorer's tree structure invites you to explore the various components of SQL Server management and development.

Each Database Engine server node includes Databases, Security, Server Objects, Replication, AlwaysOn High Availability, Management, Integration Services Catalogs, and SQL Server Agent. Most of the tree structure is fixed, but additional nodes are added as objects are created within the server.

The Databases node contains all the server's databases. When you right-click on a database, the context menu includes a host of options and commands. Under each database are standard nodes (see Figure 1), which manage the following database objects:

• Database Diagrams: Illustrates several tables and their relationships. A database may contain multiple diagrams, and each diagram does not need to display all the tables. This makes it easy to organize large databases into modular diagrams.
• Tables: Used to create and modify the design of tables, view and edit the contents of tables, and work with the tables' indexes, permissions, and publications. Triggers, stored procedures that respond to data-modification operations (insert, update, and delete), may be created and edited here.
• Views: Stored SQL statements are listed, created, and edited, and the results viewed, from this node.
• Synonyms: Alternative names for SQL Server database objects.
• Programmability: A large section that includes most of the development objects, stored procedures, functions, database triggers, assemblies, types, rules, defaults, Plan Guides and Sequences.
• Service Broker: Used to view and create Server broker objects, such as message types, contracts, queues, services, routes, remote service bindings, and broker priorities. You can now quickly create Service Broker applications using pre-defined templates. These code templates are presented when you right-click any node under Service Broker and choose to create a new item of that grouping's type.
• Storage: Used to manage nonstandard storage, such as full-text search and table partition schemes and functions, Full Text Stoplists, and Search Property Lists.
• Security: Used to manage security at the database level.

The Security node is used to manage server-wide security:

• Logins: Server-level authentication of logins.
• Server Roles: Predefined security roles. You can now also create your own custom server-lever roles.
• Credentials: Lists credentials.
• Cryptographic Providers: Used for advanced data encryption.
• Audits: Part of SQL Audit, collects data from Extended Events.
• Server Audit Specifications: Defines a SQL Audit for a server-level audit.
• Server Objects: Holds server-wide items:
  • Backup Devices: Organizes tapes and files for backup operations.
  • Endpoints: HTTP endpoints used by database mirroring, service broker, SOAP, and T-SQL.
  • Linked Servers: Lists predefined server credentials for distributed queries.
  • Triggers: Contains server level DDL triggers.

Replication is used to set up and monitor replication:

• Local Publications: Lists publications available from this server.
• Local Subscriptions: Lists subscriptions this server subscribes to from other servers.

The AlwaysOn High Availability node contains information about AlwaysOn Availability Groups.

• Availability Groups: Lists the Availability Groups configured on this server. This node will only appear if the AlwaysOn feature is enabled on the server instance.

The Management node contains several server-wide administration tools:

• Policy Management: Creates and manages Policy-Based Management policies.
• Data Collection: Defines data collection points for SQL Server's Management Data Warehouse.
• Resource Governor: Controls Enterprise Edition's CPU and Memory Resource Governor.
• Extended Events: Set up and monitor Extended Events sessions.
• Maintenance Plans: Create and manage Maintenance Plans.
• SQL Server Logs: SQL Server creates a new log with every restart of the service; view them here.
• Database Mail: Configures and monitors Database Mail.
• Distributed Transaction Coordinator: Manages DTC for transactions involving multiple servers.
• Legacy: Contains deprecated objects such as older database maintenance plans.

Integration Services Catalogs contains the SSISDB catalog, which contains the objects needed to work with Integration Services (SSIS) projects.

• SSISDB: Stores objects needed for working with Integration Services such as projects, packages, parameters, environments, and operational history.

The final node links to SQL Server Agent tools (if SQL Server Agent is running):

• Jobs: Control SQL Server Agent Jobs.
• Job Activity Monitor: View Job Activity.
• Alerts: Configure SQL Server Agent Alerts.
• Operators: Set up SQL Server Agent Operators.
• Proxies: Manage SQL Server Agent external.
• Error Logs: View SQL Server Error Logs.

Caution

Because Management Studio and SQL Server are communicating as client and server, the two processes are not always in sync. Changes on the server are often not immediately reflected in Management Studio unless Management Studio is refreshed, which is why nearly every tool has a Refresh icon, and Refresh is in nearly every context menu.

---

2. Filtering Object Explorer

Some databases are huge. To ease navigating these objects, Microsoft has included a filter for portions of the tree that include user-defined objects, such as tables or views. The Filter icon is in the toolbar at the top of the Object Explorer. The icon is enabled only when the top node for a type of user-defined object is selected. For example, to filter the tables, select the tree node, and then click the Filter icon, or right-click to open the tree's context menu, and select Filter ? Filter Settings.

The Filter Settings dialog box enables you to filter the object by name, schema, owner, or creation date. To remove the filter, use the same context menu, or open the Filter Settings dialog box and choose Clear Filter. The filter accepts only single values for each parameter; boolean operators are not permitted.

3. Object Explorer Details

The Object Explorer Details page offers lots of useful information at a glance, such as recovery model, containment type, compatibility level, collation, database owner, and much, much more. You can open the Object Explorer Details page by going to View ? Object Explorer Details or just press the F7 key.

• Object Explorer Details has dozens of additional columns that may be added to the grid. Right-click the grid headers to select additional columns.
• The columns can be rearranged and the rows sorted by any column.
• Data can be selected (highlighted) and copied to the clipboard (Ctrl+C) in a tabbed format with header columns — perfect for pasting into Excel and graphing.
• The pane below the grid displays several properties depending on the size of the pane.

The Object Explorer Details Search is one of the best kept secrets of SQL Server.

• If Object Explorer is at the server node level, the Object Explorer Details Search searches every object in the server.
• If Object Explorer is at any node at or under the database node level, it searches the current database.
• The Object Explorer Details page is rather object type generic and so is its context menu. The best solution is to use the synchronize toolbar button or context menu command to quickly jump to the object in Object Explorer.
• If the Back button in Object Explorer Detail returns to search results, it automatically reexecutes the search to be sure the list is as up-to-date as possible.

4. The Table Designer

Creating a new table, or modifying the design of an existing table, is easy with the Table Designer. The Table Designer, as shown in Figure 2, is similar to MS Access and other database design tool interfaces.

Figure 2 Using the Table Designer tool, you can create tables or edit their designs.

Create a new table by selecting the table node in the tree and then selecting New Table from the context menu. You can alter the design of existing tables by selecting the table, right-clicking, and selecting Design from the context menu.

You can individually select and edit columns in the top pane. The column properties for the selected column are listed in the bottom pane. You can open dialog boxes for modifying foreign keys and indexes using the Table Designer menu or toolbar.

Although you may prefer Query Editor to the GUI tools, the Table Designer page is a clean, straightforward UI that generates scripts for every modification. Open the Property window as well, because some table properties are visible only there.