October 13, 2010 at 9:16 am
Just wondering if anyone has a script out there that will move all objects from one filegroup to another filegroup.
i.e.- I have lots of heavily fragmented indexes and I don't like how big some of the files in a filegroup have grown. I want to say "take everything on the DATA12 filegroup and move it to DATA25". It's important that I get EVERYTHING on DATA12, because I want to be able to delete the underlying files.
I'll be repeating this with lots of filegroups, so if there is a good script out there for this, I would very much appreciate it.
October 13, 2010 at 10:30 am
Determine the tables that are on the one filegroup, and rebuild the clustered index on the new filegroup. This will move the data to the new filegroup.
If you have non-clustered indexes on the filegroup, you may need to drop and recreate on the new filegroup - not sure about that.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 13, 2010 at 11:49 am
was looking for a way to automate the process and found this script... not sure of the source, I know I pulled it off the net a while ago... maybe even from SQLServerCentral. This works for everything except heaps. For heaps, just create a clustered index.
print 'Script name: ' + @script_name
print 'Server: ' + @@servername
print 'Database: ' + db_name()
print 'User: ' + suser_sname()
print 'Start time: ' + cast(getdate() as varchar)
print replicate('-', 80)
go
-- These six SET options must be set to ON
set ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON
-- This option must be set to OFF.
set NUMERIC_ROUNDABORT OFF
/******************************************************************************
Here is a configurable part:
mapping between tables and filegroups
*******************************************************************************/
-- declare a table with desired database tables layout
if object_id('tempdb..#desired_layout') is not null drop table #desired_layout
create table #desired_layout (database_name sysname, table_name sysname, filegroup_name sysname)
insert into #desired_layout (database_name, table_name, filegroup_name)
-- DatabaseA database
select 'DATABASENAME', 'TABLENAME', 'DESTINATIONFILEGROUP' UNION ALL
--add many rows here with same format as above to move more tables
select 'DATABASENAME', 'TABLENAME', 'DESTINATIONFILEGROUP'
--union all
--select 'DatabaseA', 'system_status', 'PRIMARY' union all
--select 'DatabaseA', 'table_a_1', 'SrvA_DbA__DatabaseA' union all
-- DatabaseB database
--select 'DatabaseB', 'status_reason_type', 'SrvA_DbB_provider' union all
--select 'DatabaseB', 'table_a_1_item', 'SrvA_DbB_DatabaseB' union all
--select 'DatabaseB', 'table_a_2', 'SrvA_DbB_DatabaseB' union all
--select 'DatabaseB', 'unit_of_measure', 'SrvA_DbB_DatabaseB' union all
--select 'DatabaseB', 'workflow_status', 'SrvA_DbB_DatabaseB'
/******************************************************************************
End of configurable part,
below goes the actual code
*******************************************************************************/
go
use tempdb
go
-- a table with the column list for all the indexes in the source database is prepared,
-- but is left emtpy - it's needed to be created prior to a helper UDF
if object_id('tempdb..index_columns') is not null drop table index_columns
create table index_columns (database_name sysname, table_name sysname, index_name sysname,
column_name sysname, key_no int,
is_index_clustered bit, is_index_unique bit, is_column_descending bit)
go
-- a helper function converting a table with column names into a comma-separated list
if object_id('tempdb..f_get_column_list') is not null drop function f_get_column_list
go
create function dbo.f_get_column_list (
@database_name sysname,
@table_name sysname,
@index_name sysname
)
returns varchar(8000)
as
begin
declare @col_list varchar(8000)
select @col_list = isnull(@col_list + ', ', '') +
column_name + ' ' +
case is_column_descending when 1 then 'DESC' else '' end
from index_columns
where database_name = @database_name
and table_name = @table_name
and index_name = @index_name
order by key_no
return @col_list
end
go
-- after that point the only 'go' command is the very final one - many variables are reused.
declare
@sql varchar(8000),
@crlf char(2),
@rowcnt int
select
@crlf = char(13) + char(10),
@sql = ''
-- check for existence of all the filegroups mentioned in the layout table, exit if there is any problem:
-- get a list of existing filegroups for all the databases:
if object_id('tempdb..#existing_filegroups') is not null drop table #existing_filegroups
create table #existing_filegroups (database_name sysname, filegroup_name sysname)
select @sql = @sql + @crlf + 'select ''' + database_name + ''' as database_name, groupname as group_name from ' +
database_name + '.dbo.sysfilegroups union all'
from #desired_layout
group by database_name
select
@sql = replace (@sql + ';', 'union all;', '')
insert into #existing_filegroups (database_name, filegroup_name)
exec(@sql)
(select @rowcnt = count(distinct d.filegroup_name)
from #desired_layout d
left join #existing_filegroups e
on d.database_name = e.database_name
and d.filegroup_name = e.filegroup_name
where e.database_name is null)
if @rowcnt > 0
begin
select 'In ' + upper(d.database_name) + ' database there is no filegroup ''' + upper(d.filegroup_name) + ''''
as 'Filegroups that does not exist in the target database:'
from #desired_layout d
left join #existing_filegroups e
on d.database_name = e.database_name
and d.filegroup_name = e.filegroup_name
where e.database_name is null
group by d.database_name, d.filegroup_name
print 'Script is aborted because there is at least one filegroup that does not exist (see above).'
return
end
print 'Check for the missing filegroups completed successfully - all the filegroups mentioned in the list do exist.'
-- get the existing database/table/index/filegroup layout
if object_id('tempdb..#existing_layout') is not null drop table #existing_layout
create table #existing_layout (database_name sysname, table_name sysname, index_name sysname, ind_id int,
filegroup_name sysname, is_pk_or_uk int, table_filegroup_name sysname)
select @sql = null
select @sql = isnull(@sql + @crlf + 'union all' + @crlf, '') + 'select ''' + database_name + ''' as database_name, ' + @crlf +
'o.name as table_name, i.name as index_name, i.indid as ind_id, g.groupname as filegroup_name, ' + @crlf +
'case when k.name = i.name then 1 else 0 end as is_pk_or_uk, ' + @crlf +
'g2.groupname as table_filegroup_name ' + @crlf +
' from ' + database_name + '.dbo.sysobjects o ' + @crlf +
' join ' + database_name + '.dbo.sysindexes i ' + @crlf +
' on o.id = i.id ' + @crlf +
' join ' + database_name + '.dbo.sysfilegroups g ' + @crlf +
' on i.groupid = g.groupid ' + @crlf +
' left join (select c.id, o2.name ' + @crlf +
' from ' + database_name + '.dbo.sysconstraints c ' + @crlf +
' join ' + database_name + '.dbo.sysobjects o2 ' + @crlf +
' on c.constid = o2.id ' + @crlf +
' where o2.xtype in (''PK'', ''UQ'')) k ' + @crlf +
' on o.id = k.id ' + @crlf +
' join ' + database_name + '.dbo.sysindexes i2 ' + @crlf +
' on o.id = i2.id ' + @crlf +
' and i2.indid < 2 ' + @crlf +
' join ' + database_name + '.dbo.sysfilegroups g2 ' + @crlf +
' on i2.groupid = g2.groupid ' + @crlf +
' where o.xtype = ''U'' ' + @crlf +
' and i.name not like ''\_WA%'' escape ''\'' ' + @crlf +
' --and indexproperty(object_id(o.name), i.name, ''isstatistics'') = 0 ' + @crlf +
' and i.indid < 255 ' + @crlf
from #desired_layout
group by database_name
insert into #existing_layout (database_name, table_name, index_name, ind_id, filegroup_name, is_pk_or_uk, table_filegroup_name)
exec(@sql)
-- compose table with commands changing the database structure
if object_id('tempdb..#sql') is not null drop table #sql
create table #sql (rowid int identity (1,1), database_name sysname, table_name sysname,
index_name sysname, seq_number int, sql varchar(7000))
-- get all the indexes that must be dropped:
-- 1. indexes that are not clustered, are not created as PK or UNIQUE constraint.
-- 2. either:
-- 2a. index is located not in a desired filegroup
-- 2b. clustered index on the table (index belongs to) is located not in a desired filegroup
-- All those indexes got sequence code of 1 meaning it will be executed first.
insert #sql (database_name, table_name, index_name, seq_number, sql)
select distinct e.database_name, e.table_name, e.index_name, 1 as seq_number,
sql = 'use ' + e.database_name + '; drop index dbo.' + e.table_name + '.' + e.index_name
from #existing_layout e
join #desired_layout d
on e.database_name = d.database_name
and e.table_name = d.table_name
where e.is_pk_or_uk = 0
and e.ind_id > 1
and (
e.filegroup_name <> d.filegroup_name
or
e.table_filegroup_name <> d.filegroup_name
)
-- get all the indexes that must be created as clustered with drop_existing option:
-- 1. all the indexes that are clustered or PK or unique
-- (all the PK indexes on the tables that don't have clustered key get here as PK/UQ so the distinction needs to be done -
-- when the CLUSTERED word is included.)
-- All those indexes got sequence code of 2 meaning they will be executed when all the dependant indexes are dropped
-- The very command is to be added later - now just identifiers.
insert #sql (database_name, table_name, index_name, seq_number, sql)
select distinct e.database_name, e.table_name, e.index_name, 2 as seq_number,
sql = 'use ' + e.database_name + '; create %unique% %clustered% index ' + e.index_name + ' on dbo.' + e.table_name +
'(%column_list%) with fillfactor = 100, drop_existing on ' + quotename(d.filegroup_name)
from #existing_layout e
join #desired_layout d
on e.database_name = d.database_name
and e.table_name = d.table_name
where
e.is_pk_or_uk = 1 -- check for PK or unique key
or e.ind_id = 1 -- indid in sysindexes means clustered key on table
-- get all the indexes that are dropped in the first step - with the new destination filegroup and
-- all the parameters like fillfactor, and so on.
-- no need to analyze layouts, just take those that were marked as to be dropped and
-- compose create index statement for them.
insert #sql (database_name, table_name, index_name, seq_number, sql)
select distinct s.database_name, s.table_name, s.index_name, 3 as seq_number,
sql = 'use ' + s.database_name + '; create %unique% nonclustered index ' + s.index_name + ' on dbo.' + s.table_name +
'(%column_list%) with fillfactor = 100 on ' + quotename(d.filegroup_name)
from #sql s
join #desired_layout d
on s.database_name = d.database_name
and s.table_name = d.table_name
where seq_number = 1
-- get attributes, column lists from the existing database
select @sql = null
select @sql = isnull(@sql + @crlf, '') +
'use ' + database_name + @crlf +
'insert into tempdb..index_columns (database_name, table_name, index_name, column_name, ' + @crlf +
' key_no, is_index_clustered, is_index_unique, is_column_descending)' + @crlf +
'select ''' + database_name + ''', o.name as table_name, i.name as index_name, c.name as column_name, k.keyno, ' + @crlf +
' INDEXPROPERTY (o.id, i.name, ''isclustered'') as is_index_clustered,' + @crlf +
' INDEXPROPERTY (o.id, i.name, ''isunique'') as is_index_unique,' + @crlf +
' INDEXKEY_PROPERTY(o.id, i.indid, k.keyno, ''isdescending'') as is_column_descending' + @crlf +
' from ' + database_name + '.dbo.sysobjects o' + @crlf +
' join ' + database_name + '.dbo.sysindexes i' + @crlf +
' on o.id = i.id' + @crlf +
' join ' + database_name + '.dbo.sysindexkeys k' + @crlf +
' on i.id = k.id' + @crlf +
' and i.indid = k.indid' + @crlf +
' join ' + database_name + '.dbo.syscolumns c' + @crlf +
' on k.colid = c.colid' + @crlf +
' and k.id = c.id' + @crlf +
' where ' + @crlf +
' o.xtype = ''U''' + @crlf +
' and INDEXPROPERTY (i.id, i.name, ''isstatistics'') = 0' + @crlf +
''
from #desired_layout
group by database_name
exec(@sql)
-- replace %column_list% placeholders in the index creation statements with proper column list
update #sql
set sql = replace(sql, '%column_list%', dbo.f_get_column_list(database_name, table_name, index_name))
-- specify proper clustered option for indexes:
-- for indexes that are clustered already:
update s
set sql = replace(sql, '%clustered%', 'clustered')
from #sql s
join tempdb..index_columns ic
on s.database_name = ic.database_name
and s.table_name = ic.table_name
and s.index_name = ic.index_name
and ic.is_index_clustered = 1
-- for indexes that are PK in table without any clustered index - we make PK indexes clustered,
-- so it will be rebuilt in the new (desired) filegroup.
update s
set sql = replace(sql, '%clustered%', 'clustered')
from #sql s
join tempdb..index_columns ic
on s.database_name = ic.database_name
and s.table_name = ic.table_name
and s.index_name = ic.index_name
join #existing_layout e
on s.database_name = e.database_name
and s.table_name = e.database_name
and e.ind_id = 0 -- that means the table is a heap right now (no clustered index)
-- for the rest set it as nonclustered
update s
set sql = replace(sql, '%clustered%', 'nonclustered')
from #sql s
-- specify proper unique option for indexes:
-- for indexes that are clustered already:
update s
set sql = replace(sql, '%unique%', 'unique')
from #sql s
join tempdb..index_columns ic
on s.database_name = ic.database_name
and s.table_name = ic.table_name
and s.index_name = ic.index_name
and ic.is_index_unique = 1
-- for the rest set it as non-unique
update s
set sql = replace(sql, '%unique%', '')
from #sql s
-- at this point all the commands are prepared in #sql table
-- and all we need is just to execute them one-by-one.
-- Actions for a table are wrapped into a transaction,
-- so if there is any error no table indexes structures
print cast(getdate() as varchar) + ': Starting processing the tables'
declare
@rowid int,
@database_name sysname,
@table_name sysname,
@index_name sysname,
@seq_number int
declare t cursor static for
select distinct database_name, table_name
from #sql
order by database_name, table_name
open t
fetch next from t into @database_name, @table_name
while @@fetch_status = 0
begin
print cast(getdate() as varchar) + ': Processing table ' + @table_name + ' in ' + @database_name + ' database'
begin tran
declare i cursor static for
select rowid, index_name, sql, seq_number
from #sql
where database_name = @database_name
and table_name = @table_name
order by seq_number, index_name
open i
fetch next from i into @rowid, @index_name, @sql, @seq_number
while @@fetch_status = 0
begin
if @seq_number = 1 print cast(getdate() as varchar) + ': Dropping index ' + @table_name + '.' + @index_name
else if @seq_number = 2 print cast(getdate() as varchar) + ': Moving index ' + @table_name + '.' + @index_name + ' to another filegroup'
else if @seq_number = 3 print cast(getdate() as varchar) + ': Re-creating index ' + @table_name + '.' + @index_name
print cast(getdate() as varchar) + ': A command to be executed: ' + @sql
exec(@sql)
-- if there is an error - stop processing that table, rolling back transaction for it and reporting failure for it
if @@error <> 0
begin
rollback
print cast(getdate() as varchar) + ': Command failed to execute'
break
end
print cast(getdate() as varchar) + ': Command successfully completed'
fetch next from i into @rowid, @index_name, @sql, @seq_number
end
close i
deallocate i
-- if we have uncommitted transaction here that means no error occured in th loop on the indexes
-- and there is no transaction rollback issued above - i.e. everything is fine for that table,
-- report success on it (and commit it of course):
if @@trancount > 0
begin
commit
print cast(getdate() as varchar) + ': Successfully processed table ' + @table_name + ' in ' + @database_name + ' database'
end
fetch next from t into @database_name, @table_name
end -- of loop on t cursor
close t
deallocate t
-- get the tables layout after the changes (similar to what was done before the changes:
-- get the existing database/table/index/filegroup layout
if object_id('tempdb..#updated_layout') is not null drop table #updated_layout
create table #updated_layout (database_name sysname, table_name sysname, index_name sysname, ind_id int,
filegroup_name sysname, is_pk_or_uk int, table_filegroup_name sysname)
select @sql = null
select @sql = isnull(@sql + @crlf + 'union all' + @crlf, '') + 'select ''' + database_name + ''' as database_name, ' + @crlf +
'o.name as table_name, i.name as index_name, i.indid as ind_id, g.groupname as filegroup_name, ' + @crlf +
'case when k.name = i.name then 1 else 0 end as is_pk_or_uk, ' + @crlf +
'g2.groupname as table_filegroup_name ' + @crlf +
' from ' + database_name + '.dbo.sysobjects o ' + @crlf +
' join ' + database_name + '.dbo.sysindexes i ' + @crlf +
' on o.id = i.id ' + @crlf +
' join ' + database_name + '.dbo.sysfilegroups g ' + @crlf +
' on i.groupid = g.groupid ' + @crlf +
' left join (select c.id, o2.name ' + @crlf +
' from ' + database_name + '.dbo.sysconstraints c ' + @crlf +
' join ' + database_name + '.dbo.sysobjects o2 ' + @crlf +
' on c.constid = o2.id ' + @crlf +
' where o2.xtype in (''PK'', ''UQ'')) k ' + @crlf +
' on o.id = k.id ' + @crlf +
' join ' + database_name + '.dbo.sysindexes i2 ' + @crlf +
' on o.id = i2.id ' + @crlf +
' and i2.indid < 2 ' + @crlf +
' join ' + database_name + '.dbo.sysfilegroups g2 ' + @crlf +
' on i2.groupid = g2.groupid ' + @crlf +
' where o.xtype = ''U'' ' + @crlf +
' and i.name not like ''\_WA%'' escape ''\'' ' + @crlf +
' --and indexproperty(object_id(o.name), i.name, ''isstatistics'') = 0 ' + @crlf +
' and i.indid < 255 ' + @crlf
from #desired_layout
group by database_name
insert into #updated_layout (database_name, table_name, index_name, ind_id, filegroup_name, is_pk_or_uk, table_filegroup_name)
exec(@sql)
-- report difference between the previously existing layout, what was the intent and what we've got after the update:
print 'Script execution status report (see the grid if it is not displayed below):'
select
e.database_name + '.' + e.table_name + '.' + e.index_name as object,
e.filegroup_name as original_fileroup,
isnull(d.filegroup_name, 'NOT CONFIGURED') as desired_new_filegroup,
isnull(u.filegroup_name, 'OBJECT DISAPPEARED!!!') as actual_new_filegroup,
case
when d.filegroup_name is null then 'NOT CONFIGURED'
when u.filegroup_name is null then 'OBJECT WAS LOST'
when d.filegroup_name <> u.filegroup_name then 'NO UPDATE HAPPENED'
else 'ok'
end as update_status
from #existing_layout e
left join #updated_layout u
on e.database_name = u.database_name
and e.table_name = u.table_name
and e.index_name = u.index_name
left join #desired_layout d
on e.database_name = d.database_name
and e.table_name = d.table_name
where e.ind_id > 0 -- no need to show items for the heap (which item is not actually an index anyway).
-- If such heap table has a PK it is shown in a separate line (with ind_id > 1)
order by e.database_name, e.table_name, e.index_name
go
-- clean-up
use tempdb
if object_id('tempdb..f_get_column_list') is not null drop function f_get_column_list
if object_id('tempdb..index_columns') is not null drop table index_columns
go
-- to release any database we switched to
use master
go
-- the end of the script
go
-- script output end printed
print replicate('-', 80)
print 'Completion time: ' + cast(getdate() as varchar)
print replicate('-', 80)
go
October 13, 2010 at 11:56 am
found the first few lines, which include the author:
/*******************************************************************************
** Name: 02a. Tables moved to proper filegroups.sql
** Description: Script moves tables to proper filegroup if it's not aligned
with a list which is derived from the model(s).
Script structure:
- A list (database, table, filegroup) is created.
- Existence of all filegroups is checked - quits if there is an
assumed filegroup that does not exist in the database.
- Every table in the database is checked against that list.
- For those having different filegroup a script is built
and preserved: that script re-creates existing indexes
(creates with drop existing)
I.e. it repeats existing indexes
for the table with the new filegroup specified.
Since some of the non-clustered indexes may be located in
a different filegroup, they are moved to the table's filegroup.
See index (re-)creation script description below.
- Those scripts are executed within a transaction one-by-one.
Index (re-)creation script structure:
1) compose drop index commands list create index commands list
after existing indexes
for non-clustered indexes which are located not in the desired
filegroup or if the clustered index for the table is not in the
desired filegroup.
2) compose create index (with drop existing) commands for the clustered
indexes that are to be moved. For tables without any clustered
index the PK supporting index is made clustered and recreated with other.
3) compose create index commands list after existing indexes
for non-clustered indexes which are located not in the desired
filegroup or if the clustered index for the table is not in the
desired filegroup. The index creation command does not include
filegroup name, so it will be created in the same filegroup
as the table it belongs to.
Those commands are saved in a table with following structure:
- record id (identity)
- table name
- sequence number (the same as topics listed just above)
- index name
- sql command
The very structure manipulation script will go through that table
in order of table name then sequence number then index name
(latter is just for convinience - to analyze the log).
There will be a transaction for each table - too costly to make
it around everything.
Script comments verbosely its execution process at the run time.
Script is assumed to be a part of incremental build fixig
defect XXXXX - DatabaseB database discrepancies and a similar
one for DatabaseA. They differ only with the table content.
Script is also is assumed to be a tool for operations team
parameterized by the table with tables/filegroups
(see statements inserting data into #desired_layout table variable).
In order to get all the messages in a printable form the script
output must be directed to text or a file.
NOTE: since that script can't move tables with BLOB attribute,
such tables must be moved before by a separate script(s).
**
** Input Parameters: database/table/filegroup mapping in a form
of insert statements (see configurable part below).
** Returns: a table with database/table/index names with filegroup names
for each object: original one, configured(desired) one and the final one,
status of the operation for each index is specified.
** Modification History
** Sr Date Modified By Reason
** 1 05/01/2007 Alexander Karmanov Initial version
** 2 07/17/2007 Alexander Karmanov Added DatabaseA database tables,
added explicit option setting.
** 3 11/14/2007 Alexander Karmanov Reconfigured script:
for tables in XXXXXXXXX repository
specified PRIMARY filegroup instead of YYYYYYYYYYYYYYY.
*******************************************************************************/
use master
go
set nocount on
declare
@script_name sysname
select
@script_name = '02a. Tables moved to proper filegroups.sql'
print replicate('-', 80)
print 'Script name: ' + @script_name
print 'Server: ' + @@servername
print 'Database: ' + db_name()
print 'User: ' + suser_sname()
print 'Start time: ' + cast(getdate() as varchar)
print replicate('-', 80)
go
-- These six SET options must be set to ON
set ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON
-- This option must be set to OFF.
set NUMERIC_ROUNDABORT OFF
/******************************************************************************
Here is a configurable part:
mapping between tables and filegroups
*******************************************************************************/
-- declare a table with desired database tables layout
if object_id('tempdb..#desired_layout') is not null drop table #desired_layout
create table #desired_layout (database_name sysname, table_name sysname, filegroup_name sysname)
insert into #desired_layout (database_name, table_name, filegroup_name)
-- DatabaseA database
--/**
**/
--union all
--select 'DatabaseA', 'system_status', 'PRIMARY' union all
--select 'DatabaseA', 'table_a_1', 'SrvA_DbA__DatabaseA' union all
-- DatabaseB database
--select 'DatabaseB', 'status_reason_type', 'SrvA_DbB_provider' union all
--select 'DatabaseB', 'table_a_1_item', 'SrvA_DbB_DatabaseB' union all
--select 'DatabaseB', 'table_a_2', 'SrvA_DbB_DatabaseB' union all
--select 'DatabaseB', 'unit_of_measure', 'SrvA_DbB_DatabaseB' union all
--select 'DatabaseB', 'workflow_status', 'SrvA_DbB_DatabaseB'
/******************************************************************************
End of configurable part,
below goes the actual code
*******************************************************************************/
go
use tempdb
go
-- a table with the column list for all the indexes in the source database is prepared,
-- but is left emtpy - it's needed to be created prior to a helper UDF
if object_id('tempdb..index_columns') is not null drop table index_columns
create table index_columns (database_name sysname, table_name sysname, index_name sysname,
column_name sysname, key_no int,
is_index_clustered bit, is_index_unique bit, is_column_descending bit)
go
-- a helper function converting a table with column names into a comma-separated list
if object_id('tempdb..f_get_column_list') is not null drop function f_get_column_list
go
create function dbo.f_get_column_list (
@database_name sysname,
@table_name sysname,
@index_name sysname
)
returns varchar(8000)
as
begin
declare @col_list varchar(8000)
select @col_list = isnull(@col_list + ', ', '') +
column_name + ' ' +
case is_column_descending when 1 then 'DESC' else '' end
from index_columns
where database_name = @database_name
and table_name = @table_name
and index_name = @index_name
order by key_no
return @col_list
end
go
-- after that point the only 'go' command is the very final one - many variables are reused.
declare
@sql varchar(8000),
@crlf char(2),
@rowcnt int
select
@crlf = char(13) + char(10),
@sql = ''
-- check for existence of all the filegroups mentioned in the layout table, exit if there is any problem:
-- get a list of existing filegroups for all the databases:
if object_id('tempdb..#existing_filegroups') is not null drop table #existing_filegroups
create table #existing_filegroups (database_name sysname, filegroup_name sysname)
select @sql = @sql + @crlf + 'select ''' + database_name + ''' as database_name, groupname as group_name from ' +
database_name + '.dbo.sysfilegroups union all'
from #desired_layout
group by database_name
select
@sql = replace (@sql + ';', 'union all;', '')
insert into #existing_filegroups (database_name, filegroup_name)
exec(@sql)
(select @rowcnt = count(distinct d.filegroup_name)
from #desired_layout d
left join #existing_filegroups e
on d.database_name = e.database_name
and d.filegroup_name = e.filegroup_name
where e.database_name is null)
if @rowcnt > 0
begin
select 'In ' + upper(d.database_name) + ' database there is no filegroup ''' + upper(d.filegroup_name) + ''''
as 'Filegroups that does not exist in the target database:'
from #desired_layout d
left join #existing_filegroups e
on d.database_name = e.database_name
and d.filegroup_name = e.filegroup_name
where e.database_name is null
group by d.database_name, d.filegroup_name
print 'Script is aborted because there is at least one filegroup that does not exist (see above).'
return
end
print 'Check for the missing filegroups completed successfully - all the filegroups mentioned in the list do exist.'
-- get the existing database/table/index/filegroup layout
if object_id('tempdb..#existing_layout') is not null drop table #existing_layout
create table #existing_layout (database_name sysname, table_name sysname, index_name sysname, ind_id int,
filegroup_name sysname, is_pk_or_uk int, table_filegroup_name sysname)
select @sql = null
select @sql = isnull(@sql + @crlf + 'union all' + @crlf, '') + 'select ''' + database_name + ''' as database_name, ' + @crlf +
'o.name as table_name, i.name as index_name, i.indid as ind_id, g.groupname as filegroup_name, ' + @crlf +
'case when k.name = i.name then 1 else 0 end as is_pk_or_uk, ' + @crlf +
'g2.groupname as table_filegroup_name ' + @crlf +
' from ' + database_name + '.dbo.sysobjects o ' + @crlf +
' join ' + database_name + '.dbo.sysindexes i ' + @crlf +
' on o.id = i.id ' + @crlf +
' join ' + database_name + '.dbo.sysfilegroups g ' + @crlf +
' on i.groupid = g.groupid ' + @crlf +
' left join (select c.id, o2.name ' + @crlf +
' from ' + database_name + '.dbo.sysconstraints c ' + @crlf +
' join ' + database_name + '.dbo.sysobjects o2 ' + @crlf +
' on c.constid = o2.id ' + @crlf +
' where o2.xtype in (''PK'', ''UQ'')) k ' + @crlf +
' on o.id = k.id ' + @crlf +
' join ' + database_name + '.dbo.sysindexes i2 ' + @crlf +
' on o.id = i2.id ' + @crlf +
' and i2.indid < 2 ' + @crlf +
' join ' + database_name + '.dbo.sysfilegroups g2 ' + @crlf +
' on i2.groupid = g2.groupid ' + @crlf +
' where o.xtype = ''U'' ' + @crlf +
' and i.name not like ''\_WA%'' escape ''\'' ' + @crlf +
' --and indexproperty(object_id(o.name), i.name, ''isstatistics'') = 0 ' + @crlf +
' and i.indid < 255 ' + @crlf
from #desired_layout
group by database_name
insert into #existing_layout (database_name, table_name, index_name, ind_id, filegroup_name, is_pk_or_uk, table_filegroup_name)
exec(@sql)
-- compose table with commands changing the database structure
if object_id('tempdb..#sql') is not null drop table #sql
create table #sql (rowid int identity (1,1), database_name sysname, table_name sysname,
index_name sysname, seq_number int, sql varchar(7000))
-- get all the indexes that must be dropped:
-- 1. indexes that are not clustered, are not created as PK or UNIQUE constraint.
-- 2. either:
-- 2a. index is located not in a desired filegroup
-- 2b. clustered index on the table (index belongs to) is located not in a desired filegroup
-- All those indexes got sequence code of 1 meaning it will be executed first.
insert #sql (database_name, table_name, index_name, seq_number, sql)
select distinct e.database_name, e.table_name, e.index_name, 1 as seq_number,
sql = 'use ' + e.database_name + '; drop index dbo.' + e.table_name + '.' + e.index_name
from #existing_layout e
join #desired_layout d
on e.database_name = d.database_name
and e.table_name = d.table_name
where e.is_pk_or_uk = 0
and e.ind_id > 1
and (
e.filegroup_name <> d.filegroup_name
or
e.table_filegroup_name <> d.filegroup_name
)
-- get all the indexes that must be created as clustered with drop_existing option:
-- 1. all the indexes that are clustered or PK or unique
-- (all the PK indexes on the tables that don't have clustered key get here as PK/UQ so the distinction needs to be done -
-- when the CLUSTERED word is included.)
-- All those indexes got sequence code of 2 meaning they will be executed when all the dependant indexes are dropped
-- The very command is to be added later - now just identifiers.
insert #sql (database_name, table_name, index_name, seq_number, sql)
select distinct e.database_name, e.table_name, e.index_name, 2 as seq_number,
sql = 'use ' + e.database_name + '; create %unique% %clustered% index ' + e.index_name + ' on dbo.' + e.table_name +
'(%column_list%) with fillfactor = 100, drop_existing on ' + quotename(d.filegroup_name)
from #existing_layout e
join #desired_layout d
on e.database_name = d.database_name
and e.table_name = d.table_name
where
e.is_pk_or_uk = 1 -- check for PK or unique key
or e.ind_id = 1 -- indid in sysindexes means clustered key on table
-- get all the indexes that are dropped in the first step - with the new destination filegroup and
-- all the parameters like fillfactor, and so on.
-- no need to analyze layouts, just take those that were marked as to be dropped and
-- compose create index statement for them.
insert #sql (database_name, table_name, index_name, seq_number, sql)
select distinct s.database_name, s.table_name, s.index_name, 3 as seq_number,
sql = 'use ' + s.database_name + '; create %unique% nonclustered index ' + s.index_name + ' on dbo.' + s.table_name +
'(%column_list%) with fillfactor = 100 on ' + quotename(d.filegroup_name)
from #sql s
join #desired_layout d
on s.database_name = d.database_name
and s.table_name = d.table_name
where seq_number = 1
-- get attributes, column lists from the existing database
select @sql = null
select @sql = isnull(@sql + @crlf, '') +
'use ' + database_name + @crlf +
'insert into tempdb..index_columns (database_name, table_name, index_name, column_name, ' + @crlf +
' key_no, is_index_clustered, is_index_unique, is_column_descending)' + @crlf +
'select ''' + database_name + ''', o.name as table_name, i.name as index_name, c.name as column_name, k.keyno, ' + @crlf +
' INDEXPROPERTY (o.id, i.name, ''isclustered'') as is_index_clustered,' + @crlf +
' INDEXPROPERTY (o.id, i.name, ''isunique'') as is_index_unique,' + @crlf +
' INDEXKEY_PROPERTY(o.id, i.indid, k.keyno, ''isdescending'') as is_column_descending' + @crlf +
' from ' + database_name + '.dbo.sysobjects o' + @crlf +
' join ' + database_name + '.dbo.sysindexes i' + @crlf +
' on o.id = i.id' + @crlf +
' join ' + database_name + '.dbo.sysindexkeys k' + @crlf +
' on i.id = k.id' + @crlf +
' and i.indid = k.indid' + @crlf +
' join ' + database_name + '.dbo.syscolumns c' + @crlf +
' on k.colid = c.colid' + @crlf +
' and k.id = c.id' + @crlf +
' where ' + @crlf +
' o.xtype = ''U''' + @crlf +
' and INDEXPROPERTY (i.id, i.name, ''isstatistics'') = 0' + @crlf +
''
from #desired_layout
group by database_name
exec(@sql)
-- replace %column_list% placeholders in the index creation statements with proper column list
update #sql
set sql = replace(sql, '%column_list%', dbo.f_get_column_list(database_name, table_name, index_name))
-- specify proper clustered option for indexes:
-- for indexes that are clustered already:
update s
set sql = replace(sql, '%clustered%', 'clustered')
from #sql s
join tempdb..index_columns ic
on s.database_name = ic.database_name
and s.table_name = ic.table_name
and s.index_name = ic.index_name
and ic.is_index_clustered = 1
-- for indexes that are PK in table without any clustered index - we make PK indexes clustered,
-- so it will be rebuilt in the new (desired) filegroup.
update s
set sql = replace(sql, '%clustered%', 'clustered')
from #sql s
join tempdb..index_columns ic
on s.database_name = ic.database_name
and s.table_name = ic.table_name
and s.index_name = ic.index_name
join #existing_layout e
on s.database_name = e.database_name
and s.table_name = e.database_name
and e.ind_id = 0 -- that means the table is a heap right now (no clustered index)
-- for the rest set it as nonclustered
update s
set sql = replace(sql, '%clustered%', 'nonclustered')
from #sql s
-- specify proper unique option for indexes:
-- for indexes that are clustered already:
update s
set sql = replace(sql, '%unique%', 'unique')
from #sql s
join tempdb..index_columns ic
on s.database_name = ic.database_name
and s.table_name = ic.table_name
and s.index_name = ic.index_name
and ic.is_index_unique = 1
-- for the rest set it as non-unique
update s
set sql = replace(sql, '%unique%', '')
from #sql s
-- at this point all the commands are prepared in #sql table
-- and all we need is just to execute them one-by-one.
-- Actions for a table are wrapped into a transaction,
-- so if there is any error no table indexes structures
print cast(getdate() as varchar) + ': Starting processing the tables'
declare
@rowid int,
@database_name sysname,
@table_name sysname,
@index_name sysname,
@seq_number int
declare t cursor static for
select distinct database_name, table_name
from #sql
order by database_name, table_name
open t
fetch next from t into @database_name, @table_name
while @@fetch_status = 0
begin
print cast(getdate() as varchar) + ': Processing table ' + @table_name + ' in ' + @database_name + ' database'
begin tran
declare i cursor static for
select rowid, index_name, sql, seq_number
from #sql
where database_name = @database_name
and table_name = @table_name
order by seq_number, index_name
open i
fetch next from i into @rowid, @index_name, @sql, @seq_number
while @@fetch_status = 0
begin
if @seq_number = 1 print cast(getdate() as varchar) + ': Dropping index ' + @table_name + '.' + @index_name
else if @seq_number = 2 print cast(getdate() as varchar) + ': Moving index ' + @table_name + '.' + @index_name + ' to another filegroup'
else if @seq_number = 3 print cast(getdate() as varchar) + ': Re-creating index ' + @table_name + '.' + @index_name
print cast(getdate() as varchar) + ': A command to be executed: ' + @sql
exec(@sql)
-- if there is an error - stop processing that table, rolling back transaction for it and reporting failure for it
if @@error <> 0
begin
rollback
print cast(getdate() as varchar) + ': Command failed to execute'
break
end
print cast(getdate() as varchar) + ': Command successfully completed'
fetch next from i into @rowid, @index_name, @sql, @seq_number
end
close i
deallocate i
-- if we have uncommitted transaction here that means no error occured in th loop on the indexes
-- and there is no transaction rollback issued above - i.e. everything is fine for that table,
-- report success on it (and commit it of course):
if @@trancount > 0
begin
commit
print cast(getdate() as varchar) + ': Successfully processed table ' + @table_name + ' in ' + @database_name + ' database'
end
fetch next from t into @database_name, @table_name
end -- of loop on t cursor
close t
deallocate t
-- get the tables layout after the changes (similar to what was done before the changes:
-- get the existing database/table/index/filegroup layout
if object_id('tempdb..#updated_layout') is not null drop table #updated_layout
create table #updated_layout (database_name sysname, table_name sysname, index_name sysname, ind_id int,
filegroup_name sysname, is_pk_or_uk int, table_filegroup_name sysname)
select @sql = null
select @sql = isnull(@sql + @crlf + 'union all' + @crlf, '') + 'select ''' + database_name + ''' as database_name, ' + @crlf +
'o.name as table_name, i.name as index_name, i.indid as ind_id, g.groupname as filegroup_name, ' + @crlf +
'case when k.name = i.name then 1 else 0 end as is_pk_or_uk, ' + @crlf +
'g2.groupname as table_filegroup_name ' + @crlf +
' from ' + database_name + '.dbo.sysobjects o ' + @crlf +
' join ' + database_name + '.dbo.sysindexes i ' + @crlf +
' on o.id = i.id ' + @crlf +
' join ' + database_name + '.dbo.sysfilegroups g ' + @crlf +
' on i.groupid = g.groupid ' + @crlf +
' left join (select c.id, o2.name ' + @crlf +
' from ' + database_name + '.dbo.sysconstraints c ' + @crlf +
' join ' + database_name + '.dbo.sysobjects o2 ' + @crlf +
' on c.constid = o2.id ' + @crlf +
' where o2.xtype in (''PK'', ''UQ'')) k ' + @crlf +
' on o.id = k.id ' + @crlf +
' join ' + database_name + '.dbo.sysindexes i2 ' + @crlf +
' on o.id = i2.id ' + @crlf +
' and i2.indid < 2 ' + @crlf +
' join ' + database_name + '.dbo.sysfilegroups g2 ' + @crlf +
' on i2.groupid = g2.groupid ' + @crlf +
' where o.xtype = ''U'' ' + @crlf +
' and i.name not like ''\_WA%'' escape ''\'' ' + @crlf +
' --and indexproperty(object_id(o.name), i.name, ''isstatistics'') = 0 ' + @crlf +
' and i.indid < 255 ' + @crlf
from #desired_layout
group by database_name
insert into #updated_layout (database_name, table_name, index_name, ind_id, filegroup_name, is_pk_or_uk, table_filegroup_name)
exec(@sql)
-- report difference between the previously existing layout, what was the intent and what we've got after the update:
print 'Script execution status report (see the grid if it is not displayed below):'
select
e.database_name + '.' + e.table_name + '.' + e.index_name as object,
e.filegroup_name as original_fileroup,
isnull(d.filegroup_name, 'NOT CONFIGURED') as desired_new_filegroup,
isnull(u.filegroup_name, 'OBJECT DISAPPEARED!!!') as actual_new_filegroup,
case
when d.filegroup_name is null then 'NOT CONFIGURED'
when u.filegroup_name is null then 'OBJECT WAS LOST'
when d.filegroup_name <> u.filegroup_name then 'NO UPDATE HAPPENED'
else 'ok'
end as update_status
from #existing_layout e
left join #updated_layout u
on e.database_name = u.database_name
and e.table_name = u.table_name
and e.index_name = u.index_name
left join #desired_layout d
on e.database_name = d.database_name
and e.table_name = d.table_name
where e.ind_id > 0 -- no need to show items for the heap (which item is not actually an index anyway).
-- If such heap table has a PK it is shown in a separate line (with ind_id > 1)
order by e.database_name, e.table_name, e.index_name
go
-- clean-up
use tempdb
if object_id('tempdb..f_get_column_list') is not null drop function f_get_column_list
if object_id('tempdb..index_columns') is not null drop table index_columns
go
-- to release any database we switched to
use master
go
-- the end of the script
go
-- script output end printed
print replicate('-', 80)
print 'Completion time: ' + cast(getdate() as varchar)
print replicate('-', 80)
go
edited to remove personal information
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply