Few SQL questions

stryk wrote:

Well, it's tricky business, first you have to rename all original SIFT tables, then re-create them - original name - in the new filegroup. Then copy the data from the old SIFT tables into the new ones (Same procedure when partitioning tables). Sounds simple, but requires some smart TSQL scripting ... or has anybody an easier way for this task? Maybe some utility? Hmm, maybe I'll try to generate one :idea:

cnicola wrote:

2) I read the thread but still don't feel answers my question.

cnicola wrote:

3) Keep me posted if you get a script. I thought you would be able to do it from the Management Studio.

/*********************************************************/
/***              STRYK System Improvement             ***/
/***    Performance Optimization & Troubleshooting     ***/
/***  (c) 2007, STRYK System Improvement, Jörg Stryk   ***/
/***                   www.stryk.info                  ***/
/*********************************************************/

if exists (select * from dbo.sysobjects 
where id = object_id(N'[dbo].[ssi_movesift]') and objectproperty(id, N'IsProcedure') = 1)
drop procedure [dbo].[ssi_movesift]
go

create procedure dbo.ssi_movesift
  @filegroup varchar(128) = '',    -- Hides info-messages
  @script tinyint = 1              -- Create just TSQL script 
with encryption
as

print '---------------------------------------------------------'
print '---              STRYK System Improvement             ---'
print '---    Performance Optimization & Troubleshooting     ---'
print '---  (c) 2007, STRYK System Improvement, Jörg Stryk   ---'
print '---                   www.stryk.info                  ---'
print '---------------------------------------------------------'
print '---           Version 0.90, Date: 14.07.2007          ---'
print ''

if not exists(select * from sys.filegroups where [name] = @filegroup) begin
  raiserror ('ERROR: Invalid Parameter @filegroup: %i', 15, 1, @filegroup)
  return
end
if (@script not in (0,1)) begin
  raiserror ('ERROR: Invalid Parameter @script: %i', 15, 1, @script)
  return
end

declare @sift_tab_id int, 
        @fld_name varchar(128),
        @nci_id int,
        @nci_name varchar(128), 
        @statement varchar(8000)

set nocount on
set statistics io off
set transaction isolation level serializable

if @script = 1 begin
  print '/* Begin >> */'
  print 'USE [' + db_name() + ']'
  print 'GO'
  print 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'
end else
  print 'Moving SIFT tables to filegroup "' + @filegroup + '" ...'

-- temporary table for sorting by table-name
create table #sift_tab ([tab_id] int, [tab_name] varchar(250))
insert into #sift_tab select [object_id], [name] from sys.objects where 
  (objectproperty([object_id], N'IsUserTable') = 1) and ([name] like ('%$' + '[0-9]' + '%')) and right([name], 1) in ('0','1','2','3','4','5','6','7','8','9')

-- cursor SIFT tables
declare sift_tab_cur cursor fast_forward for select [tab_id] from #sift_tab order by [tab_name]
open sift_tab_cur
fetch next from sift_tab_cur into @sift_tab_id
while @@fetch_status = 0 begin
  -- check for valid SIFT record
  if (not exists(select [name] from sys.columns where ([object_id] = @sift_tab_id) and ([name] like 's' + '[0-9]' + '%')))
    or
     (not exists(select [name] from sys.columns where ([object_id] = @sift_tab_id) and ([name] like 'f' + '[0-9]' + '%')))
    or
     (exists(select [name] from sys.columns where ([object_id] = @sift_tab_id) and ([name] like 's' + '[0-9]' + '%') and (type_name([user_type_id]) <> 'decimal')))
    or
     (exists(select [name] from sys.columns where ([object_id] = @sift_tab_id) and ([name] = 'timestamp')))
    goto End_Step

  -- Create statement
  set @statement =
    'BEGIN TRANSACTION
ALTER TABLE dbo.[' + object_name(@sift_tab_id) + '] DROP CONSTRAINT [' + object_name(@sift_tab_id) + '_idx] WITH (MOVE TO [' + @filegroup + '])'

  set @statement = @statement + 
'
ALTER TABLE dbo.[' + object_name(@sift_tab_id) + '] WITH NOCHECK ADD CONSTRAINT [' + object_name(@sift_tab_id) + '_idx] PRIMARY KEY CLUSTERED ([bucket],'

  -- Constraint Field cursor
  declare fld_cur cursor fast_forward for select [name] from sys.columns where [object_id] = @sift_tab_id and [name] like 'f%'
  open fld_cur
  fetch next from fld_cur into @fld_name
  while @@fetch_status = 0 begin
    set @statement = @statement + '[' + @fld_name + '],'
    fetch next from fld_cur into @fld_name
  end
  close fld_cur
  deallocate fld_cur

  set @statement = left(@statement, len(@statement) - 1)
  set @statement = @statement + ') ON [' + @filegroup + ']'

  -- NCI cursor
  if exists(select * from sys.indexes where ([object_id] = @sift_tab_id) and (isnull(indexproperty([object_id],[name], 'IsClustered'),0) = 0) and (isnull(indexproperty([object_id],[name], 'IsAutoStatistics'),0) = 0) and (isnull(indexproperty([object_id],[name], 'IsStatistics'),0) = 0)) begin
    declare nci_cur cursor fast_forward for select [index_id],[name] from sys.indexes where ([object_id] = @sift_tab_id) and (isnull(indexproperty([object_id],[name], 'IsClustered'),0) = 0) and (isnull(indexproperty([object_id],[name], 'IsAutoStatistics'),0) = 0) and (isnull(indexproperty([object_id],[name], 'IsStatistics'),0) = 0)
    open nci_cur
    fetch next from nci_cur into @nci_id, @nci_name
    while @@fetch_status = 0 begin
      set @statement = @statement +
'
CREATE INDEX [' + @nci_name + '] ON dbo.[' + object_name(@sift_tab_id) + '] (' 

      declare fld_cur cursor fast_forward for 
        select SC.[name] from sys.index_columns IC 
        join sys.columns SC on SC.[object_id] = IC.[object_id] and SC.[column_id] = IC.[column_id] 
        where (IC.[object_id] = @sift_tab_id) and (IC.[index_id] = @nci_id) and (IC.[is_included_column] = 0)
        order by IC.[key_ordinal]
      open fld_cur
      fetch next from fld_cur into @fld_name
      while @@fetch_status = 0 begin
        set @statement = @statement + '[' + @fld_name + '],'
        fetch next from fld_cur into @fld_name
      end
      close fld_cur
      deallocate fld_cur

      set @statement = left(@statement, len(@statement) - 1)
      set @statement = @statement + ')'

      if exists(select * from sys.index_columns where ([object_id] = @sift_tab_id) and ([index_id] = @nci_id) and ([is_included_column] = 1)) begin
        set @statement = @statement + ' INCLUDE('

        declare fld_cur cursor fast_forward for 
          select SC.[name] from sys.index_columns IC 
          join sys.columns SC on SC.[object_id] = IC.[object_id] and SC.[column_id] = IC.[column_id] 
          where (IC.[object_id] = @sift_tab_id) and (IC.[index_id] = @nci_id) and (IC.[is_included_column] = 1)
          order by IC.[key_ordinal]
        open fld_cur
        fetch next from fld_cur into @fld_name
        while @@fetch_status = 0 begin
          set @statement = @statement + '[' + @fld_name + '],'
          fetch next from fld_cur into @fld_name
        end
        close fld_cur
        deallocate fld_cur

        set @statement = left(@statement, len(@statement) - 1)
        set @statement = @statement + ')'
      end

      set @statement = @statement + ' WITH (DROP_EXISTING = ON) ON [' + @filegroup + ']'

      fetch next from nci_cur into @nci_id, @nci_name
    end
    close nci_cur
    deallocate nci_cur
  end

  set @statement = @statement +
'
COMMIT TRANSACTION
GO'

  if @script = 1
    print @statement
  else begin
    begin transaction
    exec(@statement)
    if @@trancount > 0 commit transaction
  end  
  End_Step:
  fetch next from sift_tab_cur into @sift_tab_id
end
close sift_tab_cur
deallocate sift_tab_cur
drop table #sift_tab
if @@trancount > 0 commit transaction
if @script = 1
  print '/* << End */'
else
  print 'Finished.'

exec ssi_movesift
   [@filegroup =] {'FilegroupName'}
   [,] [@script = {0|1 (default)}

exec ssi_movesift 'SIFTGroup'

BEGIN TRANSACTION
ALTER TABLE dbo.[CRONUS International Ltd_$17$0] DROP CONSTRAINT [CRONUS International Ltd_$17$0_idx] WITH (MOVE TO [SIFTGroup])
ALTER TABLE dbo.[CRONUS International Ltd_$17$0] WITH NOCHECK ADD CONSTRAINT [CRONUS International Ltd_$17$0_idx] PRIMARY KEY CLUSTERED ([bucket],[f3],[f4]) ON [SIFTGroup]
CREATE INDEX [CRONUS International Ltd_$17$0_hlp_idx] ON dbo.[CRONUS International Ltd_$17$0] ([f4]) WITH (DROP_EXISTING = ON) ON [SIFTGroup]
CREATE INDEX [ssi_CovIdx] ON dbo.[CRONUS International Ltd_$17$0] ([bucket],[f3],[f4],[s17],[s53],[s54],[s68],[s69],[s70]) WITH (DROP_EXISTING = ON) ON [SIFTGroup]
COMMIT TRANSACTION
GO