Going beyond sp_helpindex

Every SQL Server developer and DBA has to use the sp_helpindex from time to time to get some information on the indexes on a table. I have 2 major problems with this stored procedure:

• All information is concatenated in 1 string
• Not much info on partitioned indexes

If I run the following statement:

exec sp_helpindex 'Customer'

The following information will be returned:

index_name	index_description	index_keys
pk_entity	clustered, unique, primary key located on PRIMARY	entity

CREATE FUNCTION dbo.fn_helpindex(@table_name sysname)
RETURNS @result table(table_name            sysname, 
                      index_name            sysname, 
                      is_unique             bit, 
                      ignore_dup_key        bit, 
                      is_primary_key        bit, 
                      is_unique_constraint  bit, 
                      fill_factor           tinyint, 
                      is_padded             bit, 
                      is_disabled           bit, 
                      is_hypothetical       bit, 
                      allow_row_locks       bit, 
                      allow_page_locks      bit, 
                      data_space_type       nvarchar(2),
                      data_space            nvarchar(1024))
AS
  BEGIN
    
    WITH partitionfields ([object_id], [index_id], [name]) AS 
    (SELECT ic.[object_id] ,ic.index_id, c.name 
       FROM sys.index_columns ic
       JOIN sys.columns c 
         ON  ic.[object_id] = c.[object_id] 
        AND ic.column_id = c.column_id 
      WHERE ic.partition_ordinal = 1)
    INSERT INTO @result
    SELECT table_name = object_name(i.[object_id])
         , i.name

         , i.is_unique
         , i.ignore_dup_key
         , i.is_primary_key
         , i.is_unique_constraint
         , i.fill_factor
         , i.is_padded
         , i.is_disabled
         , i.is_hypothetical
         , i.allow_row_locks
         , i.allow_page_locks
         , data_space_type = ds.type
         , data_space = coalesce(ds.name + N'(' + pf.name + N')', ds.name)
    FROM sys.indexes i 
      INNER JOIN sys.data_spaces ds
      ON  i.data_space_id = ds.data_space_id
      LEFT JOIN partitionfields pf
      ON i.[object_id] = pf.[object_id] AND
         i.index_id = pf.index_id
    WHERE i.name IS NOT NULL 
      AND object_name(i.[object_id]) = @table_name

    -- Return the result of the function
    RETURN 
  END

If I run:

select * from dbo.fn_helpindex('Customer')

The following information will be returned:

table_name	index_name	is_clustered	is_unique	ignore_dup_key	is_primary_key	is_unique_constraint	fill_factor	is_padded	is_disabled	is_hypothetical	allow_row_locks	allow_page_locks	data_space_type	data_space
Customer	pk_entity	1	1	0	1	0	0	0	0	0	1	1	FG	PRIMARY

If we use the function on a table with a partitioned index (like the script below) the function will return more information about the data space as well.

CREATE PARTITION FUNCTION pfn_maturity_bucket (int)
AS RANGE RIGHT FOR VALUES (3, 6, 12);

CREATE PARTITION SCHEME psc_maturity_bucket
AS PARTITION pfn_maturity_bucket
ALL TO ([PRIMARY])

CREATE TABLE LoanDetails(code nvarchar(50), amount numeric(23,3), maturity_bucket int)
CREATE INDEX ixLoanDetails 
          ON LoanDetails(maturity_bucket) 
          ON psc_maturity_bucket(maturity_bucket)

So, let's run the following statement on the partitioned table:

select * from dbo.fn_helpindex('LoanDetails')

The output will be:

table_name	index_name	is_clustered	is_unique	ignore_dup_key	is_primary_key	is_unique_constraint	fill_factor	is_padded	is_disabled	is_hypothetical	allow_row_locks	allow_page_locks	data_space_type	data_space
LoanDetails	ixLoanDetails	0	0	0	0	0	0	0	0	0	1	1	PS	psc_maturity_bucket(maturity_bucket)

How to determine the default database path in all SQL Server Versions

When you create a database in SQL Server using the "CREATE DATABASE" statement without any options, the database files will be created in some "default" locations. Many people think this default location is the same as the location of the master database, but this is incorrect, it is possible to alter the location in server properties after installation of the server. (in Management Studio right click on the server and select "Server Properties", then go to the "Database Settings" tab and near the bottom you will see a section with "Database Default locations") For example, suppose I execute the following statement:

CREATE DATABASE DefaultLocationDB

On my machine this means 2 files will be created:

• C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DefaultLocationDB.mdf
• C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DefaultLocationDB.ldf

This is nice, you don't have to remember a path to pass, but when you want to restore a database from another server (where a different path is used) it can be a pain. To restore a database with a specific location for the files we use the following syntax:

RESTORE DATABASE DefaultLocationDB 
FROM  DISK = N'c:\backups\DemoDB.bak' WITH  FILE = 1,  
MOVE N'demo_data_device' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoDb.ldf',  
MOVE N'demo_log_device' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoDb.ldf',  
NOUNLOAD, REPLACE

To make it easy to restore a database using a script I designed a function to figure out the location for the default Data path (and another one for the default log path). The locations of these paths I got from http://msdn.microsoft.com/en-us/library/ms143547.aspx The output can be stored in a 260 character variable (260 as specified MAX_PATH in http://msdn.microsoft.com/en-us/library/aa365247.aspx).

Edit: Rudi Larno posted a very nice refactored version of the code I posted. (especially note the nice recursion in it.)

View my old code

IF object_id('dbo.fn_get_default_data_path') is not null
    DROP FUNCTION dbo.fn_get_default_data_path
GO

CREATE FUNCTION dbo.fn_get_default_data_path()
returns nvarchar(260)
AS
BEGIN
    DECLARE @instance_name nvarchar(200), @system_instance_name nvarchar(200), @registry_key nvarchar(512), @data_path nvarchar(260), @log_path nvarchar(260);
 
    SET @instance_name = COALESCE(convert(nvarchar(20), serverproperty('InstanceName')), 'MSSQLSERVER')
    
    -- sql 2005/2008 with instance
    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;
    SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer';
 
    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultData', @data_path output;

    IF @data_path is null -- sql 2005/2008 default instance
      BEGIN
        SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\Setup';
        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'SQLDataRoot', @data_path output;
        SET @data_path = @data_path + '\Data';
      END
 
    if @data_path is null -- sql 2000 with instance
      begin
        SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer';
        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultData', @data_path output;
      end
      
    if @data_path is null -- sql 2000 default instance
      begin
        SET @registry_key = N'Software\Microsoft\MSSQLServer\MSSQLServer';
        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultData', @data_path output;
      end
      
      return @data_path
END
GO

As you can see it gets very ugly very fast. To extract the log path use this function:

IF object_id('dbo.fn_get_default_log_path') is not null
    DROP FUNCTION dbo.fn_get_default_log_path
GO

CREATE FUNCTION dbo.fn_get_default_log_path()
RETURNS nvarchar(260)
AS
BEGIN
    DECLARE @instance_name nvarchar(200), @system_instance_name nvarchar(200), @registry_key nvarchar(512), @log_path nvarchar(260);
 
    SET @instance_name = COALESCE(convert(nvarchar(20), serverproperty('InstanceName')), 'MSSQLSERVER');
    
    -- sql 2005/2008 instance
    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;
    SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer';
     
    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultData', @data_path output;
    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultLog', @log_path output;
 
    if @log_path is null -- sql 2000 with instance
      begin
        SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer';
        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultLog', @log_path output;
      end
      
    if @log_path is null -- sql 2000 default instance
      begin
        SET @registry_key = N'Software\Microsoft\MSSQLServer\MSSQLServer';
        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultLog', @log_path output;
      end    
    
    IF @log_path is null
      BEGIN
        SELECT @log_path = dbo.fn_get_default_data_path()
      END
      
      return @log_path
END
GO

IF object_id('dbo.fn_get_default_path') is not null
    DROP FUNCTION dbo.fn_get_default_path
GO

CREATE FUNCTION dbo.fn_get_default_path(@log bit)
RETURNS nvarchar(260)
AS
BEGIN
    DECLARE @instance_name nvarchar(200), @system_instance_name nvarchar(200), @registry_key nvarchar(512), @path nvarchar(260), @value_name nvarchar(20);

    SET @instance_name = COALESCE(convert(nvarchar(20), serverproperty('InstanceName')), 'MSSQLSERVER');

    -- sql 2005/2008 with instance
    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;
    SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer';

    SET @value_name = N'DefaultData'
    IF @log = 1
      BEGIN
        SET @value_name = N'DefaultLog';
      END

    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, @value_name, @path output;

    IF @log = 0 AND @path is null -- sql 2005/2008 default instance
      BEGIN
        SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\Setup';
        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'SQLDataRoot', @path output;
        SET @path = @path + '\Data';
      END

    IF @path IS NULL -- sql 2000 with instance
      BEGIN
        SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer';
        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, @value_name, @path output;
      END

    IF @path IS NULL -- sql 2000 default instance
      BEGIN
        SET @registry_key = N'Software\Microsoft\MSSQLServer\MSSQLServer';
        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, @value_name, @path output;
      END

    IF @log = 1 AND @path is null -- fetch the default data path instead.
      BEGIN
        SELECT @path = dbo.fn_get_default_path(0)
      END

    RETURN @path;
END
GO

The functions can then be used as follows (or you can just copy paste the output):

declare @sql nvarchar(2000), 
        @data varchar(260), 
        @log varchar(260); 

select @data = dbo.fn_get_default_data_path(),
       @log = dbo.fn_get_default_log_path()

select @sql= 'RESTORE DATABASE DefaultLocationDB 
FROM  DISK = N''c:\backups\DemoDB.bak'' WITH  FILE = 1,  
MOVE N''demo_data_device'' TO N''' + @data + '\DemoDb.ldf'',  
MOVE N''demo_log_device'' TO N''' +  @log + '\DemoDb.ldf'',  
NOUNLOAD, REPLACE'

exec (@sql)

edit: I updated the script for dbo.fn_get_default_log_path() to avoid calling dbo.fn_get_default_data_path() when not needed.

Building a small Virtual PC with windows xp and SQL Server 2005 Developer edition

I love Virtual PC 2007, but I hate how every VPC image I used so far is at least 5GB and easily grows to 10GB. It is hard to keep multiple images active on your machine. So I started looking for a way to reduce the size of an image.

About the only usefull resource I found was the site of http://www.bold-fortune.com. This website contains the log of a guy that deletes windows files one by one and tests his system for stability.

My goal was to end up with the smallest possible Windows XP installation with the SQL Server 2005 service installed (and running).

Here is how I got there:

• Created a Virtual PC with Virtual PC 2007 with 128Mb memory enabled.
• Installed Windows XP SP2 and switched off as many of the optional stuff as I could.
• Set the swap file size to a fixed 128MB
• Installed virtual pc additions.
• Disabled the Windows Security center
• Disabled windows file protection (following the method described on the bold-fortune site)
• Installed the SQL Server service with the following components: Database Server, Shared components, Replication support.
• In The surface configuration wizard enable remote connections over tcp/ip.
• Disable all protocols except tcp/ip in the server configuration manager.
• Followed the deletion steps as described by bold-fortune except "WMI" and "Logical Disk manager". (also left the file drvmain.sdb)
  Between every delete I recycled SQL Server to see if I could still connect remotely and run queries afterwards.

When a in my view important service or component was deleted I restarted the vpc to see if everything was still in working order. (I check in event viewer for errors.)

Another small fix I did was running the following in a cmd.exe window:

net config server /autodisconnect:-1

This fixes most of the "MRxSmb the redirector failed to determine the connection type on the loopback connection"

When all this was done I switched on compression on the complete c-drive of the vpc (make sure sql server is running because it does not when the system databases are compressed). Then used the Whitney Defrag utility to defragment the drive. This defragmenter is extremely nice on virtual pc's because it has a tiny installation footprint and it moves all the clusters to the start of the disk.

When all is ready it is time to start compacting the Virtual PC. Start this process by capturing the ISO file "C:\Program Files\Microsoft Virtual PC\Virtual Machine Additions\Virtual Disk Precompactor.iso". This will automatically start zeroing out all the free space of the virtual hard disk.

Once the virtual hard disk is pre-compacted, shut down your Virtual Machine and compact the virtual hard disk by using the "Virtual Disk Wizard".

This (lenghty) process delivered me a Virtual PC of 826 MB and with compression on a footprint of 550 MB on disk which enables me to keep several virtual machines around. I do realise that a vpc with so little memory is slow and not usable for any serious testing, but it enables me to comfortably experiment with clustering and replication.

You can download the batch files i created to shrink my vpc images but be carefull. If you run these files on your normal pc, they WILL delete a LOT of files and certain software will NOT WORK anymore. So please be carefull. And I do not guarantee that these batch files will work perfectly for you. For me they do exactly what I need and leave me with a good usable VPC image for testing.

So If you want to try the same thing I did, you can download the batch files and start deleting windows.

A small update to the sp_help_partition procedure.

One of the flaws in the sp_help_partition function I posted earlier is the lack of information if the partition function is a Range RIGHT or a Range LEFT function. To make that clearer I added a check on the boundary_value_on_right field from the system tables. Also added an order by to make the presentation nicer.

SELECT
          object_name(i.[object_id]) as [Table],
          ps.name                    as PartitionScheme,
          pf.name                    as PartitionFunction,
          dds.destination_id         as PartitionNumber,
          fg.Name                    as FileGroupName,
          case when boundary_value_on_right = 1 then 'RIGHT' else 'LEFT' end [Range], 
          prv.value                  as RangeValue
    FROM sys.indexes i
      INNER JOIN sys.partition_schemes ps on ps.data_space_id = i.data_space_id
      INNER JOIN sys.partition_functions pf on ps.function_id = pf.function_id
      INNER JOIN sys.destination_data_spaces dds on dds.partition_scheme_id = ps.data_space_id
      INNER JOIN sys.filegroups fg on fg.data_space_id = dds.data_space_id
      LEFT  JOIN sys.partition_range_values prv on prv.boundary_id = dds.destination_id
    WHERE i.type = 1 

I hope this version works better for you. Download the complete script.

Zero Padding a numeric value in Transact SQL

A quick one before I go home (and just because I used it 10 minutes ago)... I zero pad a numeric value like this:

DECLARE @mynumber INT,
        @padding  INT
SELECT @mynumber = 123, 
       @padding = 8
SELECT REPLACE(STR(@mynumber, @padding), ' ', '0')

The result of this example will be

00000123

I admit, it is not very elegant, but is string manipulation ever elegant in TSQL...

Using SET IDENTITY_INSERT as a normal database user in SQL Server 2005.

In earlier versions of Microsoft SQL Server it was impossible to grant "SET IDENTITY_INSERT" rights to a normal user. To have permission to do an identity_insert (even as part of a stored procedure) you needed to grant the user ddl admin rights which is quite a heavy permission to grant to a normal user.

Luckily this problem can be solved in SQL Server 2005 by using Certificates and code signing.

Let's set up a demo environment.

create database identity_insert_test
use identity_insert_test
exec sp_addlogin 'SimpleLogin', 'PasswordForSimpleLogin'
GO
exec sp_grantdbaccess 'SimpleLogin', 'SimpleLogin'
GO

if exists(select * from sys.objects where object_id = object_id('dbo.a')) 
   drop table dbo.a
create table dbo.a (keyfield int identity, somevalue varchar(10))
GO

if exists(select * from sys.objects where object_id = object_id('p_a')) 
  drop procedure p_a
go
create procedure p_a
as
set identity_insert dbo.a on
insert into dbo.a (keyfield, somevalue) values (2,'nice')
set identity_insert dbo.a off

return (0)
go

grant exec on p_a to SimpleLogin
go

When we run the stored procedure with the simple "no rights" login we quickly see the following error

Msg 1088, Level 16, State 11, Procedure p_a, Line 3
Cannot find the object "dbo.a" because it does not exist or you do not have permissions.

(Make sure you run the "revert;" part of the statement to revert to your login after the error.)

Execute as user = 'SimpleLogin'
exec p_a
revert;
GO

To fix this annoying problem we need to create a few more objects in the database. First of all we need a master key which will be used to encrypt the certificate. Then we create a certificate, a login based on the certificate and we grant the "ALTER ANY SCHEMA" permission to the login. And finally we will sign the stored procedure with the schema login.

CREATE MASTER KEY ENCRYPTION BY password = '**somethingspecial**';
CREATE CERTIFICATE SchemaCert WITH SUBJECT = 'Schema user Certificate'
CREATE USER SchemaCertUser FROM CERTIFICATE SchemaCert
GRANT ALTER ANY SCHEMA TO SchemaCertUser
ADD SIGNATURE TO p_a BY CERTIFICATE SchemaCert
GO

Now when we rerun the stored procedure as our simple login we see that everything works as expected.

Execute as user = 'SimpleLogin'
exec p_a
revert;
GO

The ability to sign stored procedures gives you the power to build a much more secure SQL Server Database system. For example you can sign stored procedures that use dynamic sql and remove the need to grant select rights on the underlying tables.

I have not (yet) benchmarked the performance impact of signing stored procedures, but I suspect the overhead will not be huge.

You can download the complete test script and try it out.

a Daylight savings time aware version of a sqlclr xp_getfiledetails procedure.

One of the undocumented extended stored procedures that no longer exists in SQL Server 2005 is xp_getfiledetails.

Of course I am aware that I am not the first one to implement this procedure as a SQLCLR procedure.

I found implementations on the following locations:

• SSIS Junkie Blog by Jamie Thomson
• Simple Talk by Greg Larsen

But a bug I noticed in both the versions of the procedure is the fact that it is not "Daylight Savings Time" aware.

This means that if you request the file details of a file that is created on a day when Winter Time was in place and currently Summer Time is in use that the timestamps are off 1 hour.

To correct this problem this I wrapped the datetime checks in the following function:

private static TimeSpan ExplorerBias(DateTime timeStamp)
{
    TimeSpan bias = new TimeSpan(0);
    if (TimeZone.CurrentTimeZone.IsDaylightSavingTime(DateTime.Now) != TimeZone.CurrentTimeZone.IsDaylightSavingTime(timeStamp))
    {
        bias = TimeZone.CurrentTimeZone.GetUtcOffset(DateTime.Now) - TimeZone.CurrentTimeZone.GetUtcOffset(timeStamp);
    }
    return bias;
}

You can download the Complete xp_getfiledetails sqlclr Script and see the effect of the function.

To test the script, create a new Visual Studio 2005 Database project and paste the code from the script (or just include the file.)

Using a schema collection to validate output of FOR XML RAW

Schema collections are used in SQL Server 2005 to create an xml datatype field that is validated with an XML Schema. Unfortunately there is an xsd feature that is not implemented very nicely is the processContents="lax" attribute. The attribute processContents is implemented, but where normal xsd supports the values strict, lax and skip. The sql server schema collection parser only supports strict and skip. And unfortunately using lax does not degrade, it just gives a compilation error. So reusing prebuild xsd files is going to create some issues that need to be resolved before you can compile them in the database. I will show an example where using the processContents="lax" technique would be usefull. The following schema can be used to create a datatype to store a result of a "for xml raw" query. When you set processContents="lax" you will get a compilation error. Although it would be usefull, we have to be content with processContents="skip".

IF EXISTS(SELECT * FROM sys.schemas WHERE name ='xml_raw_schema')
    DROP XML SCHEMA COLLECTION  dbo.xml_raw_schema
GO
CREATE XML SCHEMA COLLECTION xml_raw_schema AS
 N'<?xml version="1.0"?>
 <xsd:schema xmlns:targetNamespace="http://schemas.mycompany.com/myproduct"
            elementFormDefault="qualified"
            attributeFormDefault="unqualified"
            xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <xsd:element name="table">
     <xsd:complexType>
        <xsd:sequence>
           <xsd:element name="row" minOccurs="0" maxOccurs="unbounded">
              <xsd:complexType>
                 <xsd:anyAttribute processContents="skip" />
              </xsd:complexType>
           </xsd:element>
        </xsd:sequence>
     </xsd:complexType>
   </xsd:element>
</xsd:schema>'

You can use the schema collection like this:

declare @xml xml(xml_raw_schema)
SET @xml = (select * from sys.tables FOR XML RAW, TYPE, ROOT('table'))
select @xml

Which shows (in my test case) the following output:

<table>
  <row name="Orders" object_id="2073058421" schema_id="1" parent_object_id="0" type="U " type_desc="USER_TABLE" create_date="2007-04-12T17:30:08.150" modify_date="2007-04-12T17:46:17.043" is_ms_shipped="0" is_published="0" is_schema_published="0" lob_data_space_id="0" max_column_id_used="3" lock_on_bulk_load="0" uses_ansi_nulls="1" is_replicated="0" has_replication_filter="0" is_merge_published="0" is_sync_tran_subscribed="0" has_unchecked_assembly_data="0" text_in_row_limit="0" large_value_types_out_of_row="0" />
</table>

Although we have to be carefull that it does not slow down processing too much I find it a very valuable technique because you can achieve a very basic level of type safety.

Wouldn't it be usefull if sp_who showed the query the user is running.

I can't remember the number of times I have run sp_who2 and then dbcc inputbuffer (##) to quickly check out blocking problems. Wouldn't it be usefull if this information was included in sp_who. Well, with SQL Server 2005 we can finally do this thanks to the power of the function sys.dm_exec_sql_text(). All I really had to change to sp_who2 was adding "OUTER apply sys.dm_exec_sql_text(s.sql_handle)" and then use the stmt_start and stmt_end fields to extract the substring from the query.

Install the procedure below and run:

exec sp_whoo

Executing the procedure will give you the following results:

SPID	Status	Login	HostName	BlkBy	DBName	Command	CPUTime	DiskIO	LastBatch	ProgramName	SPID	REQUESTID	SqlStatement
52	sleeping	sa	MyLaptop	.	test_db	AWAITING COMMAND	110	43	05/24 10:54:40	Microsoft SQL Server Management Studio - Query	52	0	select * from sys.tables

Changes versus the standard sp_who2 in SQL Server 2005:

• Removed all spids before 50 (system threads)
• Removed your own spid.

Limitations:

• Queries running through the service broker do not always return a sql handle. So no data can be shown.
• When accessing encrypted objects, no information can be shown either

You can download the script sp_whoo.sql or read the code below:

USE master
GO
IF (EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_whoo' AND OBJECTPROPERTY(id, 'IsProcedure') = 1))
      DROP PROCEDURE dbo.sp_whoo
GO
CREATE PROCEDURE dbo.sp_whoo(@loginame sysname = NULL)
AS
 
SET NOCOUNT ON
 
declare @retcode         int
 
declare @sidlow         varbinary(85)
       ,@sidhigh        varbinary(85)
       ,@sid1           varbinary(85)
       ,@spidlow         int
       ,@spidhigh        int
       ,@spidself        int
 
declare @charMaxLenLoginName      varchar(6)
       ,@charMaxLenDBName         varchar(6)
       ,@charMaxLenCPUTime        varchar(10)
       ,@charMaxLenDiskIO         varchar(10)
       ,@charMaxLenHostName       varchar(10)
       ,@charMaxLenProgramName    varchar(10)
       ,@charMaxLenLastBatch      varchar(10)
       ,@charMaxLenCommand        varchar(10)
 
declare @charsidlow              varchar(85)
       ,@charsidhigh             varchar(85)
       ,@charspidlow              varchar(11)
       ,@charspidhigh             varchar(11)
 
SELECT @retcode = 0      -- 0=good ,1=bad.
      ,@sidlow  = convert(varbinary(85), (replicate(char(0), 85)))
      ,@sidhigh = convert(varbinary(85), (replicate(char(1), 85)))
 
SELECT @spidlow         = 50
      ,@spidhigh        = 32767
      ,@spidself        = @@spid
 
--------------------------------------------------------------
IF (@loginame IS     NULL)  --Simple default to all LoginNames.
      GOTO LABEL_17PARM1EDITED
 
-- select @sid1 = suser_sid(@loginame)
SELECT @sid1 = NULL
IF EXISTS(SELECT * FROM sys.syslogins WHERE loginname = @loginame)
    SELECT @sid1 = sid FROM sys.syslogins WHERE loginname = @loginame
 
IF (@sid1 IS NOT NULL)  --Parm is a recognized login name.
   begin
   SELECT @sidlow  = suser_sid(@loginame)
         ,@sidhigh = suser_sid(@loginame)
   GOTO LABEL_17PARM1EDITED
   end
 
--------
 
IF (lower(@loginame collate Latin1_General_CI_AS) IN ('active'))  --Special action, not sleeping.
   begin
   SELECT @loginame = lower(@loginame collate Latin1_General_CI_AS)
   GOTO LABEL_17PARM1EDITED
   end
 
--------
 
IF (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0)  --Is a number.
   begin
   SELECT
             @spidlow   = convert(int, @loginame)
            ,@spidhigh  = convert(int, @loginame)
   GOTO LABEL_17PARM1EDITED
   end
 
--------
 
raiserror(15007,-1,-1,@loginame)
SELECT @retcode = 1
GOTO LABEL_86RETURN
 
 
LABEL_17PARM1EDITED:
 
 
--------------------  Capture consistent sysprocesses.  -------------------
 
SELECT s.spid , s.status ,s.sid ,s.hostname ,s.program_name ,s.cmd ,s.cpu ,s.physical_io ,s.blocked ,s.dbid
 , convert(sysname, rtrim(s.loginame)) AS loginname
 , s.spid AS 'spid_sort'
 , substring( convert(varchar, s.last_batch, 111) ,6  ,5 ) + ' ' + substring(convert(varchar, s.last_batch, 113), 13, 8 ) AS 'last_batch_char'
 , s.request_id
 , case when s.stmt_start=0 AND s.stmt_end=0 then qt.text
        else substring(qt.text, (s.stmt_start/2)+1,
                (case when s.stmt_end = -1
                      then len(convert(nvarchar(max), qt.text))*2
                      else s.stmt_end 
                 end - s.stmt_start) / 2) 
   end AS 'sql_statement'
INTO #tb1_sysprocesses
FROM master.dbo.sysprocesses s WITH (nolock)
  OUTER apply sys.dm_exec_sql_text(s.sql_handle) AS qt
WHERE ((s.spid > @spidlow AND s.spid < @spidhigh) OR (s.spid = @spidlow AND s.spid = @spidhigh))
  AND s.spid <> @spidself
  AND (s.sid = @sidlow OR @sidlow <> @sidhigh)
 
IF @@error <> 0
    begin
        SELECT @retcode = @@error
        GOTO LABEL_86RETURN
    end
 
--------Screen out any rows?
 
IF (@loginame IN ('active'))
  BEGIN
    DELETE #tb1_sysprocesses
    WHERE lower(status) = 'sleeping'
      AND upper(cmd)    IN ('AWAITING COMMAND','LAZY WRITER','CHECKPOINT SLEEP')
      AND blocked       = 0
  END
 
 
--------Prepare to dynamically optimize column widths.
SELECT
    @charsidlow     = convert(varchar(85),@sidlow)
   ,@charsidhigh    = convert(varchar(85),@sidhigh)
   ,@charspidlow    = convert(varchar, @spidlow)
   ,@charspidhigh   = convert(varchar, @spidhigh)
 
SELECT
     @charMaxLenLoginName   = convert( varchar, isnull( max( datalength(loginname)) ,5))
    ,@charMaxLenDBName      = convert( varchar, isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6))
    ,@charMaxLenCPUTime     = convert( varchar, isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7))
    ,@charMaxLenDiskIO      = convert( varchar, isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6))
    ,@charMaxLenCommand     = convert( varchar, isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7))
    ,@charMaxLenHostName    = convert( varchar, isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8))
    ,@charMaxLenProgramName = convert( varchar, isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11))
    ,@charMaxLenLastBatch   = convert( varchar, isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9))
FROM #tb1_sysprocesses
 
--------Output the report.
 
EXEC('
SET nocount off
SELECT
             SPID          = convert(char(5),spid)
            ,Status        =
                  CASE lower(status)
                     When ''sleeping'' Then lower(status)
                     Else                   upper(status)
                  END
            ,Login         = substring(loginname,1,' + @charMaxLenLoginName + ')
            ,HostName      =
                  CASE hostname
                     When Null  Then ''  .''
                     When '' '' Then ''  .''
                     Else    substring(hostname,1,' + @charMaxLenHostName + ')
                  END
 
            ,BlkBy         =
                  CASE               isnull(convert(char(5),blocked),''0'')
                     When ''0'' Then ''  .''
                     Else            isnull(convert(char(5),blocked),''0'')
                  END
            ,DBName        = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ')
            ,Command       = substring(cmd,1,' + @charMaxLenCommand + ')
            ,CPUTime       = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')
            ,DiskIO        = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')
            ,LastBatch     = substring(last_batch_char,1,' + @charMaxLenLastBatch + ')
            ,ProgramName   = substring(program_name,1,' + @charMaxLenProgramName + ')
            ,SPID          = convert(char(5),spid)  --Handy extra for right-scrolling users.
            ,REQUESTID     = convert(char(5),request_id)
            ,SqlStatement  = rtrim(ltrim(replace(sql_statement, char(13)+char(10), '' '')))
      from
             #tb1_sysprocesses  --Usually DB qualification is needed in exec().
      -- (Seems always auto sorted.)   order by spid_sort
')
 
LABEL_86RETURN:
IF (object_id('tempdb..#tb1_sysprocesses') IS NOT NULL)
            DROP TABLE #tb1_sysprocesses
 
RETURN @retcode -- sp_whoo
GO

Fix SQL Server management studio Ctrl+Tab annoyance

In SQL Server 2000 Query analyzer hitting Ctrl+Tab would advance you one query window in the list of open query windows. Unfortunately In Sql Server Management Studio this will display the default Visual Studio Tab overview window. This window has one huge flaw and that is that it only displays the first 25 characters of the description of the open window. Which results in the Case of SQL Server Management Studio in a list with 10 times the same item.

To revert the Ctrl+Tab and the Ctrl+Shift+Tab behaviour back to the way it was in previous versions of the SQL Server client tools you can do the following hack. Close all instances of SQL Server Management Studio and open the following xml file in a text editor:

C:\Documents and Settings\<your user name>\My Documents\SQL Server Management Studio\Settings\CurrentSettings-####-##-##.vssettings

Note that the ####-##-## characters form a date (most likely the installation/service pack installation date.) and you sometimes can have multiple versions of this file. Updating the file with the latest date should work. (I have no pc's with multiple versions of this file.) In the file look for the following string. (The file will have a few very long lines of text in it.)

<UserShortcuts />

Once you found it, replace it with the xml code below and when you restart Management studio using Ctrl+Tab and Ctrl+Shift+Tab will once again switch you to the following or previous code window without showing the useless dialog.

<UserShortcuts>
  <RemoveShortcut Command="Window.NextDocumentWindowNav" Scope="Global">Ctrl+Tab</RemoveShortcut>
  <Shortcut Command="Window.NextDocumentWindow" Scope="Global">Ctrl+Tab</Shortcut> 
  <Shortcut Command="Window.NextDocumentWindow" Scope="Query Designer">Ctrl+Tab</Shortcut>
  <RemoveShortcut Command="Window.PreviousDocumentWindowNav" Scope="Global">Ctrl+Shift+Tab</RemoveShortcut>
  <Shortcut Command="Window.PreviousDocumentWindow" Scope="Global">Ctrl+Shift+Tab</Shortcut>
  <Shortcut Command="Window.PreviousDocumentWindow" Scope="Query Designer">Ctrl+Shift+Tab</Shortcut>
</UserShortcuts>

Credit: I found this tip burried in a comment on this page of the Microsoft connect website. So thanks to ayeltsov for suggesting the solution to an annoying problem.

Why is viewing a partitioned table layout so difficult

For some reason the Microsoft SQL Server developers decided not to provide any tools to see how a partitioned table is built. Since it is quite important information I wrote a little sp_help_partition TSQL stored procedure to give an overview. There is an optional object parameter which allows you to filter on a specific partitioned table, or shows you all the partitions in the system.

The procedure is installed on the master database, but because the name starts with sp_... you can run it in any database.

usage:

   1:  exec sp_help_partition 'Orders'

The result of this statement could be:

Table	Partition Scheme	Partition Function	PartitionNumber	FileGroupName	RangeValue
Orders	monthly_scheme	monthly_bucket	1	History	2006-06-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	2	Partition_02	2006-07-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	3	Partition_03	2006-08-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	4	Partition_04	2006-09-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	5	Partition_05	2006-10-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	6	Partition_06	2006-11-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	7	Partition_07	2006-12-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	8	Partition_08	2007-01-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	9	Partition_09	2007-02-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	10	Partition_10	2007-03-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	11	Partition_11	2007-04-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	12	Partition_12	2007-05-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	13	Partition_01	2999-01-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	14	Partition_99	NULL

You can download the script sp_help_partition.sql or read the code below:

   1:  USE master

   2:  IF EXISTS(SELECT * FROM sys.objects WHERE name = 'sp_help_partition')

   3:    BEGIN

   4:      PRINT 'Dropping procedure sp_help_partition...'

   5:      DROP PROCEDURE sp_help_partition 

   6:    END

   7:  print 'Creating procedure sp_help_partition...'

   8:  GO 

   9:  CREATE PROCEDURE sp_help_partition (@object_name sysname = NULL)

  10:  AS

  11:   

  12:  declare @db_name sysname

  13:  select @db_name = db_name()

  14:   

  15:  if @object_name IS NULL

  16:   begin

  17:      exec ('use ' + @db_name + '

  18:      select 

  19:            object_name(i.[object_id]) as [Table],

  20:            ps.name                    as PartitionScheme,

  21:            pf.name                    as PartitionFunction,

  22:            dds.destination_id         as PartitionNumber,

  23:            fg.Name                    as FileGroupName,

  24:            prv.value                  as RangeValue

  25:      from .sys.indexes i 

  26:      inner join sys.partition_schemes ps on ps.data_space_id = i.data_space_id

  27:      inner join sys.partition_functions pf on ps.function_id = pf.function_id

  28:      inner join sys.destination_data_spaces dds on dds.partition_scheme_id = ps.data_space_id

  29:      inner join sys.filegroups fg on fg.data_space_id = dds.data_space_id

  30:      left  join sys.partition_range_values prv on prv.boundary_id = dds.destination_id

  31:      where i.type = 1 ')

  32:    end

  33:  else

  34:    begin

  35:      exec ('use ' + @db_name + '

  36:      select 

  37:            object_name(i.[object_id]) as [Table],

  38:            ps.name                    as PartitionScheme,

  39:            pf.name                    as PartitionFunction,

  40:            dds.destination_id         as PartitionNumber,

  41:            fg.Name                    as FileGroupName,

  42:            prv.value                  as RangeValue

  43:      from sys.indexes i 

  44:      inner join sys.partition_schemes ps on ps.data_space_id = i.data_space_id

  45:      inner join sys.partition_functions pf on ps.function_id = pf.function_id

  46:      inner join sys.destination_data_spaces dds on dds.partition_scheme_id = ps.data_space_id

  47:      inner join sys.filegroups fg on fg.data_space_id = dds.data_space_id

  48:      left  join sys.partition_range_values prv on prv.boundary_id = dds.destination_id

  49:      where i.type = 1 and 

  50:            i.[object_id] = object_id(''' + @object_name + ''')')

  51:    end

  52:  RETURN(0)

  53:  GO

  54:  IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'sp_help_partition' AND type = 'P')

  55:    BEGIN

  56:      PRINT 'PROCEDURE sp_help_partition has been created...'

  57:    END

  58:  ELSE

  59:    BEGIN

  60:      PRINT 'PROCEDURE sp_help_partition has NOT been created due to errors...'

  61:    END

  62:  GO

For more information about table partitioning in Microsoft SQL Server 2005 read the excellent whitepaper by Kimberley Tripp