Continuing my series on the underappreciated features of Microsoft SQL Server (based on the editorial with the same name by Andy Warren on SQLServerCentral.com), we will today look at a very simple, productivity enhancing feature of the Object explorer – filtering!

The problem

When working on a complex database schema, it often becomes confusing for the developer/administrator to locate a particular database object from a long list of available objects. Microsoft SQL Server offers two solutions to overcome this challenge.

1. Use the object search feature referenced in the post
2. Use object filtering within the Object Explorer

Using filtering within Object Explorer

Filtering within Object Explorer is a very simple and easy 3 step process. In fact, it is so simple that those who have not used this till date will start using it right away.

For the purposes of this demonstration, assume that we have a Microsoft SQL Server 2008 instance with the AdventureWorks2008 database loaded, and we need to only work on tables whose names contain the word “Person” in them.

Step 01: A. In the Object explorer, right click on the “Tables” B. Go to Filter –> Filter Settings
Step 02: Enter the object information as necessary. For our case, enter: Name = Person (If you only want to work within a schema, enter only the schema name.
Step 03: Simply click “OK” to apply the filters in the Object Explorer
Step 04: To remove filtering, all one needs to do is: A. In the Object explorer, right click on the “Tables” B. Go to Filter –> Remove Filter

Filtering is applicable to all object types within the Object Explorer. If one is to only work on a particular schema or a set of objects, it is very productive to have only those required objects filtered out. For SQL Server 2005 users, there are no bad news because this feature is available in Microsoft SQL Server 2005 as well.

In my next post, we will now explore how can we effectively use templates within SSMS (SQL Server Management Studio).

Be courteous. Drive responsibly.

SQL Server Management Studio (SSMS) is the one stop shop for almost UI-accessible features of the SQL Server, and an indispensable tool for administrators, deployment professionals and developers alike.

Anybody who has ever worked with Microsoft SQL Server ever since SQL Server 2005 came out would have used SSMS extensively. But, can you tell how many sessions to the SQL Server does a typical (default) SSMS session establish?

As they always say, there’s always a tradeoff. SSMS is a lot more than a query editor, and if you have followed a couple of my recent blog posts, you will find that I talk about the Object Explorer and Object Explorer details window within the SSMS (refer the starting point of the blog series here). Additional features means that we do have some tradeoff, which comes in the form of additional SQL Server connections.

About the DMV

Today, we will be using the Dynamic Management View (DMV) – sys.dm_exec_sessions, which is a very powerful DMV which returns one row per authenticated session on SQL Server. This DMV is a server-scope view that shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more. This view is often used to first view the current system load and to identify a session of interest, and then learn more information about that session by using other dynamic management views or dynamic management functions.

Once you start using DMVs, you will find that quite often this becomes the starting point of all your monitoring queries.

A point to be noted

A session_id in the results of the DMV – sys.dm_exec_sessions is equivalent to a SPID in the prior versions of SQL Server. Starting SQL Server 2005, the global function @@SPID returns the session_id of the caller.

In earlier versions of SQL Server, the system SPIDs (such as checkpoint, lazywriter, ghost record cleanup, etc) always had a SPID value < 50. Starting SQL 2005, however, this is no longer the case, and a system process may have any value as it’s SPID, as can a user session.

The distinction between a system process and a user process can be made via the “is_user_process” column of the DMV – sys.dm_exec_sessions. Also, since connections are always user processes, this can also be determined via sys.dm_exec_connections.

The Demonstration

1. Open a New SQL Server Management studio instance. Do not connect to any SQL Server
2. Launch the Windows Task Manager and get the Process ID for the SSMS.EXE (If not available by default, go to View -> Select Columns -> Process ID)
3. Now, open a new SQL Server Management Studio instance and connect to a SQL Server (in my case, it was VPCW2K3-SQL2K8)
4. Run the following query against the SQL Server. Notice that as of now, no results are returned
   ~~~
   –In a separate SSMS query window, run the following query with the PID
   –fetched from Step 02
   SELECT *
   FROM sys.dm_exec_sessions sdes
   WHERE sdes.host_process_id = 612
   ~~~
5. Now, in the test SSMS window (opened in Step# 1), create a new Database engine query by going to File –> New –> Database Engine Query. This time, connect to the SQL Server referenced in Step 03
6. Rerun the query in Step #4. Notice that we now get one record in the result
7. Now, view the Object Explorer by going to View->Object Explorer. Connect to the same SQL Server as connection #5
8. Rerun the query in Step #4. Notice that we now get two records in the result
9. Finally, SQL Now, view the Object Explorer Details by going to View->Object Explorer Details
10. Rerun the query in Step #4. Notice that we now get three records in the result

The Moral of the Story

Almost every feature of the SQL Server Management Studio contributes to an additional session to the SQL Server targeted. Avoid the urge to use all features at the same time on a production

server, or during troubleshooting – not only will it confuse you, but also might slow down the SQL Server (if the server is under pressure of some sort).

Finally, if the SQL Server has a database which is in single-user mode, it will be available to only one of the multiple sessions that SSMS starts. The trouble is that which connection will come first is unknown. Hence, it is always a good practice to have nothing but the query editor open when working with either single-user databases or single query modes like the DAC (Dedicated Admin connection).

So, configure your environment wisely, and the power of SSMS will be with you.

More Information on the DMVs

If you need more information on DMVs, please refer the following E-book, which is available for free download from http://www.red-gate.com/products/dba/sql-monitor/entrypage/dmv:

Name: SQL Server DMV Starter Pack

Authors: Glenn Berry, Louis Davidson and Tim Ford

Publisher: Red-Gate Software

Be courteous. Drive responsibly.

As mentioned in my previous post, I am currently developing a series of brief articles on the many underappreciated features of Microsoft SQL Server as received from the community as a response to Andy Warren’s SQLServerCentral.com’s editorial (http://www.sqlservercentral.com/articles/Editorial/71788/), which was published on December 17, 2010.

We started this journey by looking at the Object Explorer Details window(Object Explorer Details (Part I) – Underappreciated features of Microsoft SQL Server). I am sure that by now, most of you would have explored the Object Explorer details window and would have started to get used to it as part of your daily work life.

Searching in the Object Explorer Details Window

Today, we will be doing something a bit more fun and productivity enhancing. Imagine the following scenario.

Scenario:

You want to change the data-type of a column in your database, but are not sure about the impacted areas. You don’t have (or don’t have access to) any database documentation and don’t have the time to create any. What do you do?

Solution:

The Microsoft SQL Server 2008 Management Studio gives you a fast and easy way to do this very quickly and easily via the Object Explorer Details window. Here are the brief steps:

1. Navigate to the Object Explorer Details window by using the View menu or pressing the (F7) key when in the SQL Server Management Studio
2. Drill down to the database against which the search needs to be carried out
3. In the Search bar, enter the object name that you would like to search for (you can use wild-card characters)
4. Simply hit “Enter” to execute the search!

Simply right-clicking on a specific search result and choosing “Synchronize” navigates to the particular object in the Object Explorer!

Right click on a result	Click Synchronize

If no specific database is chosen, the search is executed across all databases in the entire SQL Server instance. This is very useful feature, however, can be very slow. If you happen to use this accidentally, there’s always the panic button labeled – “Stop”!

Salient Features/Benefits:

1. Copy results over to Excel – Select the set of records required (or use “Ctrl+A” for selecting on the results) and use “Ctrl+C”. Doing so puts the tab-separated results (with the headers) on the clipboard, and pasting these in Excel maintains all columns
2. The search is across all object types – Stored Procedures, Views, Indexes and Tables
3. The results grid can be sorted and columns can be chosen and rearranged as per requirements
4. No 3rd party tools required
5. No knowledge of SQL Server internals required

A drawback & Red Gate SQL Search

The search feature is good, but not complete when used out-of-the-box from the Microsoft SQL Server Management Studio. The one feature that does not come is a contains search. The search searches through the object names, but does not search within the objects. Let’s see an example:

CREATE PROCEDURE proc_tmpNakul
AS
BEGIN
    PRINT 'Employee'
END

Let’s try searching for “Employee” and for “Nakul”.

Searching for “Employee”	Searching for “Nakul”

Red Gate Software has developed a free tool for more sophisticated or in-depth searching. You can evaluate this tool, SQL Search by downloading it for free at:http://www.red-gate.com/products/sql-development/sql-search/

More Resources:

• Using Object Explorer Details and SQL Server Object Search – http://msdn.microsoft.com/en-us/library/cc646011.aspx

In conclusion:

There are a lot of other things that can be done with the Object Explorer details window. You can view dependencies, confirm whether or not an object is matching up to preset policies and a lot more. The Object Explorer Details described in Part I, along with the using Red-Gate SQL Search make for very powerful database navigation and search.

In the next post, I will be looking at how to filter objects within the Object Explorer itself.

Have a good day!

Be courteous. Drive responsibly.

As mentioned in my previous post, I am currently developing a series of brief articles on the many underappreciated features of Microsoft SQL Server as received from the community as a response to Andy Warren’s SQLServerCentral.com’s editorial (http://www.sqlservercentral.com/articles/Editorial/71788/), which was published on December 17, 2010.

We will start this journey by looking a few of the features that came out with the SQL Server Management Studio 2008. After login, the first feature that almost everyone sees and uses is the Object Explorer, but very few use the Object Explorer Details window.

The Object Explorer Details Window

Writing this article is nostalgia for me. The first Microsoft SQL Server version that I worked upon (more like studied upon) was Microsoft SQL Server 7.0. In fact, I still have one of those installed on a virtual machine at home. When you login to the Microsoft SQL Server 7.0 Enterprise Manager, you can graphically see:

Database information:	Table & Index usage:
Space Utilization:

This continued through Microsoft SQL Server 2000, but with the redesign in Microsoft SQL Server 2005, the Object Explorer lost it’s charm a bit. The Object Explorer and the Object Explorer details window were almost the same and there was no real point to using the Object Explorer Details window. All this changed in Microsoft SQL Server 2008. The Object Explorer Details window is useful once again and here’s how.

First of all, how does one launch Object Explorer in SQL Server 2008? It’s very simple. One can simply navigate to View –> Object Explorer Details, or hit the (F7) key. Follow the series of screenshots as I drill down to the database level information (images have been cropped to emphasize on the details):

Step 01: Object Explorer Base State	Step 02: Database Information

As you can see, the Database level information is already much more detailed than what SQL Server 7.0 could provide. Once you have drilled down onto the database level, you can get a host of other column options by simply right-clicking the column header. Once you have chosen the columns that you want to look at, you can rearrange them just as you would do to any other grid layout:

Column Chooser	An alternate

You can now drill down all the way to the tables and index details. For more fun, try using the Object Explorer to navigate through the database schema, and notice how the Object Explorer Details window stays synchronized with your movements!

Table details	Index details

As you can see, the Object Explorer in Microsoft SQL Server 2008 is a fully loaded, action-packed feature, very useful for developers, quality assurance engineers and administrators alike. I hope that you will immediately launch your SQL Server Management Studio instance and start playing around with the Object Explorer details window.

In the next part, I will be demonstrating how to search for objects using the Object Explorer details – a feature that was never available before Microsoft SQL Server 2008.

Have a good day!

Be courteous. Drive responsibly.

Recently, a team at the office asked me how could they fire off a query against a linked server. The condition was that the server where the query would originate from was going to be a Microsoft SQL Server 2008 instance whereas the target was Microsoft SQL Server 2005 instance. The information that they would be getting would be:

1. SQL Server Instance name
2. Database Name
3. Schema Name
4. Table Name

Based on this information, they wanted to find out if a given table existed on the given database on the given instance. In all cases, the instance name would either be the local instance or a linked server.

Here’s the solution I proposed to them:

Step 01: Add a Microsoft SQL Server 2005 linked server

USE master;
GO
EXEC sp_addlinkedserver 
   'VPCW2K3',
   N'SQL Server'
GO

Step 02: Write a stored procedure that builds and executes a dynamic SQL statement

Here is the stored procedure that I wrote as a POC to help them out:

CREATE PROCEDURE dbo.proc_CheckTableExistance @tInstanceName NVARCHAR(200),
                                              @tDatabaseName NVARCHAR(100),
                                              @tSchemaName   NVARCHAR(100),
                                              @tTableName    NVARCHAR(100)
AS
BEGIN
    DECLARE @stmnt NVARCHAR(MAX)
    --Check if the provided server is a linked server or not
    IF EXISTS (SELECT data_source FROM sys.servers ss WHERE ss.data_source = @tInstanceName AND ss.is_linked = 0)
    BEGIN
        SET @stmnt = 'SELECT IST.TABLE_NAME FROM [' + LTRIM(RTRIM(@tDatabaseName)) + '].' + 
                     'INFORMATION_SCHEMA.TABLES IST WHERE IST.TABLE_CATALOG = ' 
                                                    + '''' + LTRIM(RTRIM(@tDatabaseName)) + '''' +
                     ' AND IST.TABLE_SCHEMA = '     + '''' + LTRIM(RTRIM(@tSchemaName))   + '''' +
                     ' AND IST.TABLE_NAME = '       + '''' + LTRIM(RTRIM(@tTableName))    + ''''
    END
    ELSE IF EXISTS (SELECT data_source FROM sys.servers ss WHERE ss.data_source = @tInstanceName AND ss.is_linked = 1)
    BEGIN
        SET @stmnt = 'EXECUTE (''SELECT IST.TABLE_NAME FROM ' + @tDatabaseName +
                     '.INFORMATION_SCHEMA.TABLES IST WHERE IST.TABLE_CATALOG = ? AND IST.TABLE_SCHEMA = ? AND IST.TABLE_NAME = ?'', ''' 
                                + @tDatabaseName + ''', ''' 
                                + @tSchemaName   + ''',''' 
                                + @tTableName + ''') AT [' + @tInstanceName + ']'
    END
    ELSE
    BEGIN
        SET @stmnt = 'SELECT ''Required SQL instance not found in sys.servers catalog view.'''
    END
    
    EXEC sp_executesql @stmnt
END
GO

The key point here is the usage of the EXECUTE statement. The EXECUTE statement has the provision to execute a query against a linked server when combined with the “AT” clause. More details are available from the MSDN page here.

In Conclusion

Please note that there is one major point that you need to take care before using EXECUTE in this way. EXECUTE is prone to SQL Injections and hence please make sure that you production code has each and every input validated beforehand.

You can, in this way, use the EXECUTE…AT combination to execute a query against a SQL Server of a different version or even against a different data source (the MSDN example has a way to fire off a query against an ORACLE data source).

Do let me know what you, the reader, uses to query a different database on a different SQL Server instance or a different data source.

Be courteous. Drive responsibly.