Truncate partition, need to disable some indexes, need to keep track of constraints referencing partitioned tables.

• Roust_m

  SSCoach

  Points: 17408

  More actions

  October 12, 2010 at 5:57 pm

  #239664

  Hi,

  I've got a database with partitioned tables, which have some indexes not partitioned due to them being unique constraints/PKs and not having the partitioning key among their columns.

  If I need to truncate a partition, I have to disable the not partitioned indexes and later rebuild them.

  I managed to automate the process with the below script, however, after disabling one of the indexes I get a warning that a FK constraint has been disabled:

  Warning: Foreign key 'fk_xyz_d_C541A375' on table 'MYTable' referencing table 'MYTable2' was disabled as a result of disabling the index 'PK_MYTable2'.

  I am wondering if there is an efficient way to keep track of any FKs being disabled so to re-enable them once the partition truncation is finished. Similar to the below script.

  Instead of just re-enabling this particular FK, I want the solution to be scallable and to be able to pick up any FKs which have been disabled as a result of disabling the indexes.

  Any ideas?

  Thanks.

  set nocount on

  declare @tbname sysname

  declare @schname sysname

  declare @indexname sysname

  declare @cmd nvarchar(1000)

  declare @indexlist table (schname sysname, tbname sysname, indexname sysname, is_disabled tinyint)

  insert into @indexlist (schname, tbname, indexname, is_disabled)

  select distinct ss.[name], object_name(p.object_id), si.[name], si.is_disabled

  FROM sys.dm_db_partition_stats p (nolock)

  join sys.objects so (nolock)

  on p.object_id = so.object_id

  join sys.schemas ss (nolock)

  on so.schema_id = ss.schema_id

  join sys.indexes si

  on object_name(si.object_id) = object_name(p.object_id)

  where partition_number <> 1 and si.data_space_id = 1

  select @indexname = min(indexname) from @indexlist

  select @tbname = tbname from @indexlist where indexname = @indexname

  select @schname = schname from @indexlist where indexname = @indexname

  while @indexname is not null

  begin

  select @cmd = 'ALTER INDEX ' + @indexname + ' ON ' + @schname + '.' + @tbname + ' DISABLE'

  select @cmd

  exec sp_executesql @cmd

  --select @tbname, @schname

  select @indexname = min(indexname) from @indexlist where indexname > @indexname

  select @tbname = tbname from @indexlist where indexname = @indexname

  select @schname = schname from @indexlist where indexname = @indexname

  end

  set nocount off