Kill All SPIDs for given Databases

So as promised in my last post, I'm going to include a script that will loop over a list of databases and kill all SPIDS connected to them. If you want to use this, you'll need to install the SP_WHO3 proc and the StringToTable function that I posted in the past two posts.

DECLARE @DBsToKill VARCHAR(MAX) = 'DB1,DB2,DB3' --List of DBs here, comma delimited
  ,@KillTest VARCHAR(20) = 'TEST' --TEST=print SPIDs; KILL=Kill SPIDs
  ,@SQL VARCHAR(MAX)

SET @SQL = '
SET NOCOUNT ON
CREATE TABLE #SpidsToKill (
 SPID VARCHAR(5)
 ,[Status] VARCHAR(max)
 ,[login] VARCHAR(max)
 ,[HostName] VARCHAR(max)
 ,BlkBy VARCHAR(max)
 ,DBName VARCHAR(100)
 ,Command VARCHAR(max)
 ,CPUTime VARCHAR(max)
 ,DiskIO VARCHAR(max)
 ,LastBatch VARCHAR(MAX)
 ,ProgramName VARCHAR(MAX)
 ,SPID2 VARCHAR(max)
 ,RequestID VARCHAR(max)
)
'
DECLARE @DBName VARCHAR(100)

DECLARE Z_CURSOR CURSOR FOR
 SELECT TableValue
 FROM dbo.StringToTable(@DBsToKill,',')
 
OPEN Z_CURSOR
FETCH NEXT FROM Z_CURSOR 
INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
 
 SET @SQL += '
 INSERT INTO #SpidsToKill

        ( SPID
        ,Status
        ,login
        ,HostName
        ,BlkBy
        ,DBName
        ,Command
        ,CPUTime
        ,DiskIO
        ,LastBatch
        ,ProgramName
        ,SPID2
        ,RequestID
        )
EXEC sp_who3 ''' + @DBName + '''
'

 FETCH NEXT FROM Z_CURSOR 
 INTO @DBName
END
CLOSE Z_CURSOR 
DEALLOCATE Z_CURSOR

IF (@KillTest = 'Kill')
BEGIN
 SET @SQL += '
 DECLARE @SPID VARCHAR(4)
  ,@InternalSQL VARCHAR(MAX) = ''''

 DECLARE Z_CURSOR CURSOR FOR
  SELECT SPID 
  FROM #SpidsToKill
  
 OPEN Z_CURSOR
 FETCH NEXT FROM Z_CURSOR 
 INTO @SPID
 WHILE @@FETCH_STATUS = 0
 BEGIN
  SET @InternalSQL += ''
KILL '' + @SPID

  FETCH NEXT FROM Z_CURSOR 
  INTO @SPID
 END
 CLOSE Z_CURSOR 
 DEALLOCATE Z_CURSOR

 exec(@InternalSQL)
 --PRINT @InternalSQL
 '
END
ELSE
BEGIN
 SET @SQL += 'SELECT * FROM #SpidsToKill ORDER BY DBName, SPID'

END 
SET @SQL += '
DROP TABLE #SpidsToKill'

--PRINT @SQL

EXEC(@SQL)

Returning SPIDS for a given Database

I took the SP_WHO2 proc and altered it so that it could take in a parameter containing a database name and return only SPIDs that are active in that database. I pretty much just use this when I'm going to be restoring backups and need to have the database without any connections to it. However in my next post, we'll make use of this proc and the string to table proc I discussed in yesterdays post and use them both to create a script to kill all spids in a list of databases.

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_who3]    Script Date: 04/16/2010 15:23:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER procedure [dbo].[sp_who3]  --- 1995/11/03 10:16
    @dbname     sysname = NULL
as

set nocount on

declare
    @retcode         int

declare
    @sidlow         varbinary(85)
   ,@sidhigh        varbinary(85)
   ,@sid1           varbinary(85)
   ,@dbid   INT
   ,@spidlow         int
   ,@spidhigh        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)

-- defaults

select @retcode         = 0      -- 0=good ,1=bad.
select @sidlow = convert(varbinary(85), (replicate(char(0), 85)))
select @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))

select
    @spidlow         = 0
   ,@spidhigh        = 32767

--------------------------------------------------------------
IF (@dbname IS     NULL)  --Simple default to all LoginNames.
      GOTO LABEL_17PARM1EDITED

-- select @sid1 = suser_sid(@dbname)
select @dbid = null
if exists(select * from sys.databases WHERE name = @dbname)
 select @dbid = database_id from sys.databases WHERE name = @dbname

IF (@dbid IS NOT NULL)  --Parm is a recognized login name.
   begin
   GOTO LABEL_17PARM1EDITED
   end

--------

raiserror(15007,-1,-1,@dbname)
select @retcode = 1
GOTO LABEL_86RETURN


LABEL_17PARM1EDITED:


--------------------  Capture consistent sysprocesses.  -------------------

select

  spid
 ,status
 ,sid
 ,hostname
 ,program_name
 ,cmd
 ,cpu
 ,physical_io
 ,blocked
 ,dbid
 ,convert(sysname, rtrim(loginame))
        as loginname
 ,spid as 'spid_sort'

 ,  substring( convert(varchar,last_batch,111) ,6  ,5 ) + ' '
  + substring( convert(varchar,last_batch,113) ,13 ,8 )
       as 'last_batch_char'
 ,request_id

      into    #tb1_sysprocesses
      from sys.sysprocesses with (nolock)

if @@error <> 0
 begin
  select @retcode = @@error
  GOTO LABEL_86RETURN
 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
      where
             spid >= @spidlow
      and    spid <= @spidhigh



--------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)
      from
             #tb1_sysprocesses  --Usually DB qualification is needed in exec().
      where
             dbid = ' + @dbid + '

      -- (Seems always auto sorted.)   order by spid_sort

SET nocount on
'
)


LABEL_86RETURN:


if (object_id('tempdb..#tb1_sysprocesses') is not null)
            drop table #tb1_sysprocesses

return @retcode -- sp_who2

Converting a delimited string into a table

This isn't anything new, but we will be building upon this in the next post. At times you want to take a delimited string and have it returned in a table format so you can loop over it or do stuff with it; many people have done this before and I'm sure that I'm drawing on stuff I've seen before. This function works well, you call it like this:

SELECT  *
FROM    dbo.StringToTable('a,b,c,d',',')

Where the first parameter is the string to be converted and the second is the delimiter

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--/***********************************************************************
--***
--*** Function:    [StringToTable]
--*** Purpose:      Takes a delimited string and returns a table
--***   
--***   
--*** Author:    Eric Zierdt
--*** Date Created:   2010-04-15
--*** 
--*** Revision History
--*** Date   Author   Description
--*** 2010-04-15 ez    Created
--*** select * from dbo.StringToTable('a,b,c,d',',')
--***********************************************************************/

CREATE FUNCTION [dbo].[StringToTable]
 (
  @String VARCHAR(MAX),
  @Delimiter CHAR(1) = ','
 )
RETURNS @ReturnList TABLE
 (
  ID INT,
  TableValue VARCHAR(MAX)
 )
AS BEGIN
 DECLARE @ArrayList TABLE
 (
  ID INT, TableValue VARCHAR(MAX)
 )
 DECLARE @Value VARCHAR(MAX)
 DECLARE @NextDelimiterLocation INT

  ---------------------------- 
 -- REMOVE LEADING DELIMITER --
  ---------------------------- 
 SET @String = LTRIM(RTRIM(@String)) + 
 CASE WHEN RIGHT(@String, 1) = @Delimiter THEN ''
 ELSE @Delimiter
 END
 
  ------------------------------------------- 
 -- SET THE LOCATION OF THE FIRST DELIMITER --
  ------------------------------------------- 
 SET @NextDelimiterLocation = ISNULL(CHARINDEX(@Delimiter, @String, 1), 0)  

  ---------------------------------------- 
 -- IF NO DELIMITER THEN SET THE WHOLE   --
 -- STRING TO THE FIRST ROW OF THE TABLE --
  ---------------------------------------- 
 IF @NextDelimiterLocation = 0
 BEGIN
  INSERT INTO @ArrayList
  ( 
   ID
   ,TableValue 
  )
  SELECT  1, @String
 END
 ELSE
 BEGIN
  DECLARE @Counter INT
  SET @Counter = 0
  WHILE @NextDelimiterLocation > 0
  BEGIN
    -----------------------------------------------------
   -- SET THE VALUE PARAM TO DATA BEFORE THE DELIMITER  --
   -- THEN LOOP TO DO THE SAME FOR THE REST OF THE LIST --
    -----------------------------------------------------
   SET @Value = LTRIM(RTRIM(LEFT(@String, @NextDelimiterLocation - 1)))
   IF LEN(@String) > 0 AND @NextDelimiterLocation <= LEN(@String)
   BEGIN
    SET @Counter = @Counter + 1
    INSERT  INTO @ArrayList ( ID, TableValue )
    SELECT  @Counter, @Value
   END
   SET @String = SUBSTRING(@String, @NextDelimiterLocation + LEN(@Delimiter), LEN(@String))
   SET @NextDelimiterLocation = CHARINDEX(@Delimiter, @String, 1)
  END
 END
 INSERT  @ReturnList 
 ( 
  ID, TableValue 
 )
 SELECT  ID, TableValue
 FROM    @ArrayList
 
    RETURN
END

Delay run until restore is done.

I had a problem the a while back where I noticed a job kept dieing because it was pulling data from a log shipped database and it was trying to pull that data while the database was restoring it's log file. So I came up with this little sp to fire right before I execute my code. What this does is, if the datase is restoring (State = 1) it performs a loop and every 5 seconds checks back to see if it is still restoring and does this until it is no longer restoring. Since I'm executing this before I do a EXEC (@SQL) it will delay the run of my code until the database is ready.



This can be edited to work for other states, or all non ONLINE states by simply changing the while loop to from @IsRestoring = 1 to @IsRestoring <> 0

I wish there was a better way of formatting this, my code doesn't look this crappy

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

/**********************************
**  Run_After_Restore_Completes  **
**  Created by Eric Zierdt       **
**********************************/

ALTER PROCEDURE [dbo].[Run_After_Restore_Completes] (@DBName VARCHAR(155))
AS

DECLARE @IsRestoring BIT

SET NOCOUNT ON

-- RUN IF THE DATABASE EXISTS
IF EXISTS (SELECT name FROM sys.databases WHERE name = @DBName)
BEGIN
 -- CHECK THE STATE OF THE DATABASE
 SELECT @IsRestoring = State 
 FROM sys.databases 
 WHERE NAME = @DBName

 -- LOOP UNTIL DATABASE IS NO LONGER RESTORING
 WHILE @IsRestoring = 1
 BEGIN
  -- SET A DELAY SO IT ISN'T RUNNING CONSTANTLY
  WAITFOR DELAY '00:00:05'
  SELECT @IsRestoring = State FROM sys.databases WHERE NAME = @DBName
 END
END

@@IDENTITY vs. SCOPE_IDENTITY()

So, I'm not doing a great job of posting, sorry about that.





This isn't new, but I wanted a proof of concept so I created a quick query to show how using @@IDENTITY won't work if you are inserting into a table that has a trigger on it which inserts into a second table. Instead you want to use SCOPE_IDENTITY()






BEGIN TRAN
CREATE TABLE TempA (ID INT IDENTITY (1, 1), TxtData VARCHAR(55))
CREATE TABLE TempB (ID INT IDENTITY (1000, 1), TAID INT, TextData VARCHAR(55))
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TempA_T01]
ON [dbo].[TempA]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO TempB
(
TAID
, TextData
)
SELECT
ID
,'System'
FROM INSERTED
END

INSERT INTO TempA
( TxtData )
VALUES ( 'Test' -- TxtData - varchar(55)
)
SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()
SELECT *
FROM TempA
SELECT *
FROM TempB

ROLLBACK

Update USE

I think one thing I'd like to see in a future release of SQL is the ability to use variables with the USE command. I should be able to do this:



DECLARE @DBName VARCHAR(10) = "MyDB"



USE @DBName





but instead I have to write some dynamic SQL, and it's just a bigger pain the rear end than it needs to be.

File Type changes on a very large table

I had a situation recently where in looking at some tables we realized that they were originally created with a number of columns set to BigInt. As we know, the BigInt type can hold values upto 9,223,372,036,854,775,807, and has a cost of 8 bytes. We don't think that we will ever have values in these specific fields bigger than the max size of the int data type (2,147,483,647) and int uses half the size (4 bytes). This was the case for around 15-20 tables on several rows in each table and each table was around 20 gb. In many of the tables every field was a BigInt, so changing the datatype to Int would save us half the storage space needed for the table. Without thinking it through one of the DBAs tried to change the data type of one of the tables (after hours) and the Tranlog went through the roof, to the point that we actually got down to almost no free space left on our tranlog drive....not good, so how to solve the problem?

These tables were setup on a partition, where each month gets its own file, this will come in handy later on.

/***********************************************
First: create staging table:
***********************************************/
1) This I do with the GUI...Right click on the table, choose Script Table As: Create To: New Query Editor Window
2) Do the same thing for indexs and triggers, etc (but here I copy to clipboard and paste into the new table window.
3) Do a replace all on the tableName to add a _Staging to the end. Note, this depending on your naming, this should add _Staging to the indexes and triggers as well.



/***********************************************
Next: determine the min date of the data:
***********************************************/

--We use a date/time dimension to store date values, so dates/times are stored as int values in all tables
SELECT MIN(TDT.DateTimeField) FROM TableName TN WITH(NOLOCK) JOIN TimeDimTable TDT WITH(NOLOCK) ON TN.TimeKeyID = TDT.TimeKeyID


/***********************************************
Next: Next we BCP out all the data for each
month and import it into our staging table.
I do this in yearly batches so that I can check
numbers to make sure everything matches as I go.
Also, stop before you get to the current month.
***********************************************/
SET NOCOUNT ON

DECLARE @sql VARCHAR(8000)
DECLARE @TblName VARCHAR (255)
DECLARE @query VARCHAR(8000)
DECLARE @dbname VARCHAR (255)
DECLARE @StartDate VARCHAR(20)
DECLARE @ThisStartDate DATETIME
DECLARE @EndDate VARCHAR(20)
DECLARE @ThisEndDate DATETIME
DECLARE @path VARCHAR(255)

SET @TblName = 'TableName'
SET @dbname = 'DBName'
SET @ThisStartdate = '01/01/2008'
SET @ThisEndDate = '02/01/2008'
SET @path = 'Z:\BCP\'

--The date you enter below needs to be the first of the month, 2 months after the last month you want to process. So 2/1/2009 means that we will process upto 12/31/2008.
WHILE @ThisEndDate < '11/01/2009'
BEGIN

PRINT 'Start Time: ' + CAST(GETDATE() AS VARCHAR(30))
PRINT 'Running for Date: ' + CAST(@ThisStartDate AS VARCHAR(20))

SELECT @startdate = MIN(TimeKeyId), @enddate = MAX(TimeKeyID)-1 FROM TimeDimTable WITH(NOLOCK) WHERE DATETIME BETWEEN @ThisStartDate AND @ThisEndDate
SET @query = 'SELECT COUNT(*) FROM ' + @dbname + '.dbo.'+ @tblname +' WITH(NOLOCK) WHERE TimeKeyID BETWEEN ' + @startdate + ' AND ' + @enddate
PRINT @query
EXEC(@query)
SELECT @startdate = MIN(TimeKeyId), @enddate = MAX(TimeKeyID)-1 FROM TimeDimTable WITH(NOLOCK) WHERE DATETIME BETWEEN @ThisStartDate AND @ThisEndDate

-- Writing Format File
PRINT 'Writing Format File: ' + CAST(GETDATE() AS VARCHAR(30))
SELECT @sql = 'bcp ' + @dbname + '.dbo.' + @tblname + ' format nul -f ' + @path + @tblname + '.fmt -t, -r \n -c -T -S' + @@ServerName
EXEC master..xp_cmdshell @sql
--PRINT @sql

-- Writing Data File
PRINT 'Writing Data File: ' + CAST(GETDATE() AS VARCHAR(30))
SELECT @sql = 'bcp ' + @query + ' queryout ' + @path + @tblname + '.csv -t, -r \n -c -T -S' + @@ServerName
EXEC master..xp_cmdshell @sql
--PRINT @sql

-- Import Data
PRINT 'Starting Import: ' + CAST(GETDATE() AS VARCHAR(30))
set @sql = 'BULK INSERT @dbname.dbo.' + @tblname + '_staging FROM ''' + @path + @tblname + '.csv''
WITH
(
ORDER (TimeKeyID ASC)
,FORMATFILE = ''' + @path + @tblname + '.fmt''
,ROWS_PER_BATCH = 33000000
,TABLOCK

)'

--PRINT @sql
EXEC (@sql)

SET @ThisStartDate = DATEADD(m,1,@ThisStartDate)
SET @ThisEndDate = DATEADD(m,1,@ThisEndDate)
PRINT 'Month Complete: ' + CAST(GETDATE() AS VARCHAR(30)) + '

'
END

PRINT 'Entire Run Complete: ' + CAST(GETDATE() AS VARCHAR(30)) + '

'


/***************************************************** Next check that the row counts match up. Here is where having the data in a partition is nice I pull the rowcount for each partition for the tables. I pase this into Excel with some code to alert me if the values dont match: =IF(B3,IF(D3<>E3,"BAD",""),"")
The data I paste into excel is:
IndexID,PartitionNumber,FileGroupName,RowCt,NewRowCt

Here is the query to get this data:
*****************************************************/
DECLARE @FileGroupName varchar(50)
,@TableName varchar(50)
SET @FileGroupName = 'Months_Partition_Im_Working_With'
SET @TableName = 'TableName'-- do this twice, adding _Staging the second time

SELECT OBJECT_NAME(p.object_id) AS TableName,
-- i.name AS IndexName,
p.index_id AS IndexID,
--NULL AS PartitionScheme,
p.partition_number AS PartitionNumber,
fg.name AS FileGroupName,
NULL AS LowerBoundaryValue,
NULL AS UpperBoundaryValue,
-- NULL AS Boundary,
p.rows
FROM sys.partitions AS p
JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id
JOIN sys.filegroups AS fg ON fg.data_space_id = i.data_space_id
WHERE OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
and object_name(p.object_id) LIKE @TableName
AND fg.NAME LIKE @FileGroupName
UNION ALL
--get info for partitioned table/indexes
SELECT OBJECT_NAME(p.object_id) AS TableName,
-- i.name AS IndexName,
p.index_id AS IndexID,
--ds.name AS PartitionScheme,
p.partition_number AS PartitionNumber,
fg.name AS FileGroupName,
prv_left.value AS LowerBoundaryValue,
prv_right.value AS UpperBoundaryValue,
-- CASE pf.boundary_value_on_right WHEN 1 THEN 'RIGHT' ELSE 'LEFT' END AS Boundary,
p.rows AS Rows
FROM sys.partitions AS p
JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes AS ps ON ps.data_space_id = ds.data_space_id
JOIN sys.partition_functions AS pf ON pf.function_id = ps.function_id
JOIN sys.destination_data_spaces AS dds2 ON dds2.partition_scheme_id = ps.data_space_id AND dds2.destination_id = p.partition_number
JOIN sys.filegroups AS fg ON fg.data_space_id = dds2.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left ON ps.function_id = prv_left.function_id AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right ON ps.function_id = prv_right.function_id AND prv_right.boundary_id = p.partition_number
WHERE OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
and object_name(p.object_id) LIKE @TableName
and fg.name LIKE @FileGroupName
-- and p.rows > 0
-- and p.index_id IN (0,1)
-- and p.partition_number = 2
ORDER BY TableName, IndexID, PartitionNumber


/*****************************************************
Next: Repeat for all months until you get to current.
We were lucky that this was for a DataWarehouse and
the data was imported hourly, so we didn't have to be
concerned about getting the current months data and
having live data coming in at the same time, so we
just did the same thing for time span
first of current month - today+1 then as soon as we
verified it, we did the swap below.

If we had to worry about live data, I would have done
a date span of first of current month to today-1 bcp
then done an after hours insert to get todays data,
within a transaction and then done the swap below:
*****************************************************/
USE [DBName]
EXEC sp_rename 'TableName.IndexName','ZZ_IndexName', 'INDEX'
--Repeat for all origional table indexes, triggers, etc.

EXEC sp_rename 'TableName_Staging.IndexName_Staging', 'IndexName', 'INDEX'
--Repeat for all _Staging table indexes, triggers, etc.


EXEC sp_rename 'TableName', 'ZZ_TableName'
EXEC sp_rename 'TableName_Staging', 'TableName'

--Now all our tables and their indexes, triggers, etc should be swapped