moving tables and index to different filegroup

I need to create a script to move all tables and indexes to a different
filegroup after all tables and indexes are created on the primary. The
reason being we don't have license to the code and we all always get
updates to the schema in vanilla format.
It seems there is no easy way to do it since there is no ALTER
TABLE command to move table to a different filegroup.
The logic I want to use is:
(a) Find the cluster index on each table (almost all of the tables have CI o
n PKY)
and move it to different filegroup.
(b) for non clustered indexes, find them by querying sysindexes, syscolumns
and sysobjects
to recreate the index columns. Then drop the index and recreate it.
(c) Similarly all NTEXT columns will be identified and moved to separate fil
egroup of its
own.
Is there anything I should pay attention to.
How do I identify cluster index by querying index table.
Thanks.Hi,
Clustered index the INDID in sysindex table will be 1.
Note:
Ensure that you take full database backup before you do this activity.
Thanks
Hari
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3fmojvF8l53lU1@.individual.net...
>I need to create a script to move all tables and indexes to a different
> filegroup after all tables and indexes are created on the primary. The
> reason being we don't have license to the code and we all always get
> updates to the schema in vanilla format.
> It seems there is no easy way to do it since there is no ALTER
> TABLE command to move table to a different filegroup.
> The logic I want to use is:
> (a) Find the cluster index on each table (almost all of the tables have CI
> on PKY)
> and move it to different filegroup.
> (b) for non clustered indexes, find them by querying sysindexes,
> syscolumns and sysobjects
> to recreate the index columns. Then drop the index and recreate it.
> (c) Similarly all NTEXT columns will be identified and moved to separate
> filegroup of its own.
> Is there anything I should pay attention to.
> How do I identify cluster index by querying index table.
> Thanks.
>|||This article talks about using SQL-DMO:
http://www.sqljunkies.com/How%20To/...43A8681900.scuk
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3fmojvF8l53lU1@.individual.net...
>I need to create a script to move all tables and indexes to a different
> filegroup after all tables and indexes are created on the primary. The
> reason being we don't have license to the code and we all always get
> updates to the schema in vanilla format.
> It seems there is no easy way to do it since there is no ALTER
> TABLE command to move table to a different filegroup.
> The logic I want to use is:
> (a) Find the cluster index on each table (almost all of the tables have CI
> on PKY)
> and move it to different filegroup.
> (b) for non clustered indexes, find them by querying sysindexes,
> syscolumns and sysobjects
> to recreate the index columns. Then drop the index and recreate it.
> (c) Similarly all NTEXT columns will be identified and moved to separate
> filegroup of its own.
> Is there anything I should pay attention to.
> How do I identify cluster index by querying index table.
> Thanks.
>|||Thanks all.
I have one more question. How do I move the log file from the default locati
on
to another one.
I would like to add a new file to the log file and remove the first file to
the log created
at the time of database creation. This has to be done in a TSQL script.
TIA.|||Data Cruncher wrote:
> Thanks all.
> I have one more question. How do I move the log file from the default
> location to another one.
> I would like to add a new file to the log file and remove the first
> file to the log created at the time of database creation. This has to
> be done in a TSQL script.
> TIA.
You could probably detach the database, move the log file, and then
reattach specifying the new log file location.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||> (c) Similarly all NTEXT columns will be identified and moved to separate filegroup of its[
vbcol=seagreen]
> own.[/vbcol]
it seems to do this in a script is a big pain. ALTER TABLE ALTER can not be
used
with a NTEXT column. I was thinking of this approach.
(a) Add a new NTEXT column with TEXTIMAGE_ON in the desired filegroup.
(b) Update table
set new_NTEXT_column = existing_NTEXT_COLUMN
(c) drop the existing_NTEXT_COLUMN
(d) rename the newly added NTEXT column to the old one which was dropped.
But the problem is (a) itself. It seems SQL Server does not have an option i
n TSQL
to add a column at the desired location. The newly added NTEXT column should
be
right before the existing NTEXT column, so that after dropping the existing
NTEXT
column, its position is taken up by this new column. This is the only way to
guarantee
that no application breaks.
ALTER TABLE ADD , adds a new column right at the end of the table. EM allows
adding a column at the desired location by hiding the complexity of a series
of steps
it does to mimic that.|||I wrote a T-SQL script that moves a table to another filegroup, and you can
download it from
http://education.sqlfarms.com/ShowPost.aspx?PostID=59
Note that you can specify whether only data pages, and/or other indexes and
constraints should be moved automatically by the script. It's fairly long,
however it was very well-tested.
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com