Changing all existing tables collations in one step

If you're wanting to change the whole collation of your database then
(in SQL 2000) the best way is to script out the db, recreate it with
the new collation as default, then DTS your data across. I think that
if you changed it table by table then every new table you create will
then have to be changed as well.
Hopefully there's something in SQL 2005 that allows a complete db
collation change, as I expect most people have run into the issue of a
database being restored from a backup from a server that used a
different collation (I often found for some reason the collation got
set to LATIN1_GENERAL_CPL_CI_AS on peoples default install of SQL for
no apparent reason).Thank you.
"Will" wrote:

> If you're wanting to change the whole collation of your database then
> (in SQL 2000) the best way is to script out the db, recreate it with
> the new collation as default, then DTS your data across. I think that
> if you changed it table by table then every new table you create will
> then have to be changed as well.
>
> Hopefully there's something in SQL 2005 that allows a complete db
> collation change, as I expect most people have run into the issue of a
> database being restored from a backup from a server that used a
> different collation (I often found for some reason the collation got
> set to LATIN1_GENERAL_CPL_CI_AS on peoples default install of SQL for
> no apparent reason).
>

Change table file group filegroup

Hi There

I am running SQL Server 2005 Enterprise Edition, i want to split my data and indexes on different drives.

In 2000 i had to recreate clustered indexes and non clustered indexes on the correct filegroups to accomplish this.

In 2005 i see there is a ALTER TABLE MOVE TO Filegroup option, thats cool.

Does this effectively do the same as rebuilding the clustered index on the new filegroup? Will this leave the other indexes of the table on the primay filegroup or move them as well ?

If i wanted to also move the non clustered indexes is there a better way to move them that drop and re-create on the new filegroup in 2005, i see the ALTER INDEX statement does not support a move to filegroup option.

In a nutshell what is the best/easiest way to move exisitng table data and indexes to new file groups in Sql Server 2005 Enterprise Edition?

Thanx

Hi

ALTER TABLE MOVE TO should only be used when droppin gthe old clustered index.

You cannot move the table and retain the index with above.

I guess the best thing to use is drop and create index. You can use ONLINE option (Enterprise Edition Only) so that there is no downtime.

Jag

|||

Hi Jag

I am not following you, i am using the ALTER table command i am not dropping any clustered index?

I was asking what is happening in the background.

Also you say "You cannot move the table and retain the index with above.", a re you saying you loose your indexes with this command, i find that hard to believe?

Please clarify?

|||

Ok

Sorry about the confusion.

What I meant was that, "MOVE TO clause is only available with ALTER TABLE when you do a DROP CONSTRAINT"

It is not available with ALTER TABLE on its own.

The command will look like this:

Code Snippet

alter table t1 drop constraint PK_t1 with (move to [second]);

You cannot have the following:

Code Snippet

alter table t1 move to [second]);

Jag

|||

Hi Jag

Ok cool i get it now.

That is really strange that you have to drop a constraint, and i would have thought 2005 would have provided an easier way to move filegroups for tables data and indexes?

So you reckon the drop an re-create clustered index is till the way to go ?

|||

Yes I think so.

Drop and create the index to move filegroups.

good luck and let us know how you get on.

Jag

|||

Hi Jag

Ok cool, i just find that weird that there is no better way in 2005, so basically i will do it exactly as i did in 2000.

ALso not so easy to do when you have to move hundreds or thousands of tables and their indexes.

Basically i have to cursor through all the tables drop indexes, dynmically re-create the index defintion from sysindexes and re-create the index, not very clean, please let me know if you can think of a better way.

Thanx

|||Hello,

So isn't there any way to move a table to another filegroup without any lost (PK;FK,RelationShip).

I have more than 100 tables in my database and i created 5 filegroups. I have move them in their filegruop. Is there any shortest way to move?

Thank you very much...|||

Hi

Exactly what i was wondering the whole time, but it seems there still is no better way to move filegroups than to have to drop and rebuild the PK clustered indexes etc, i am in the same situation, database with hundreds of tables and thousands of PK - FK relationships and no easy way to move them to new filegroups.

Thanx

|||

really big problem and you can't create a table on a filegroup (without code) when you using design tools

please solve this problem !!!

Change table file group filegroup

Hi There

I am running SQL Server 2005 Enterprise Edition, i want to split my data and indexes on different drives.

In 2000 i had to recreate clustered indexes and non clustered indexes on the correct filegroups to accomplish this.

In 2005 i see there is a ALTER TABLE MOVE TO Filegroup option, thats cool.

Does this effectively do the same as rebuilding the clustered index on the new filegroup? Will this leave the other indexes of the table on the primay filegroup or move them as well ?

If i wanted to also move the non clustered indexes is there a better way to move them that drop and re-create on the new filegroup in 2005, i see the ALTER INDEX statement does not support a move to filegroup option.

In a nutshell what is the best/easiest way to move exisitng table data and indexes to new file groups in Sql Server 2005 Enterprise Edition?

Thanx

Hi

ALTER TABLE MOVE TO should only be used when droppin gthe old clustered index.

You cannot move the table and retain the index with above.

I guess the best thing to use is drop and create index. You can use ONLINE option (Enterprise Edition Only) so that there is no downtime.

Jag

|||

Hi Jag

I am not following you, i am using the ALTER table command i am not dropping any clustered index?

I was asking what is happening in the background.

Also you say "You cannot move the table and retain the index with above.", a re you saying you loose your indexes with this command, i find that hard to believe?

Please clarify?

|||

Ok

Sorry about the confusion.

What I meant was that, "MOVE TO clause is only available with ALTER TABLE when you do a DROP CONSTRAINT"

It is not available with ALTER TABLE on its own.

The command will look like this:

Code Snippet

alter table t1 drop constraint PK_t1 with (move to [second]);

You cannot have the following:

Code Snippet

alter table t1 move to [second]);

Jag

|||

Hi Jag

Ok cool i get it now.

That is really strange that you have to drop a constraint, and i would have thought 2005 would have provided an easier way to move filegroups for tables data and indexes?

So you reckon the drop an re-create clustered index is till the way to go ?

|||

Yes I think so.

Drop and create the index to move filegroups.

good luck and let us know how you get on.

Jag

|||

Hi Jag

Ok cool, i just find that weird that there is no better way in 2005, so basically i will do it exactly as i did in 2000.

ALso not so easy to do when you have to move hundreds or thousands of tables and their indexes.

Basically i have to cursor through all the tables drop indexes, dynmically re-create the index defintion from sysindexes and re-create the index, not very clean, please let me know if you can think of a better way.

Thanx

|||Hello,

So isn't there any way to move a table to another filegroup without any lost (PK;FK,RelationShip).

I have more than 100 tables in my database and i created 5 filegroups. I have move them in their filegruop. Is there any shortest way to move?

Thank you very much...|||

Hi

Exactly what i was wondering the whole time, but it seems there still is no better way to move filegroups than to have to drop and rebuild the PK clustered indexes etc, i am in the same situation, database with hundreds of tables and thousands of PK - FK relationships and no easy way to move them to new filegroups.

Thanx

|||

really big problem and you can't create a table on a filegroup (without code) when you using design tools

please solve this problem !!!

Change owner of User Defined Data Types

Please let me know if there is a method for changing the owner of existing User Defined Data Types. I cannot drop and recreate them as the datatypes are in use.
Thanks.Try, sp_changeobjectowner.
Refer to BOL for more information.|||sp_changeobjectowner cannot be used to change the owner of UDTs as it only works with tables, views, user-defined functions and stored procedures.|||Then only way is drop and recreate.|||As I stated in my original post, it is not possible to drop them as they are in use.|||Use specified SP to change owner for that table, which would be applied by default. Apart from this I have no other idea.