Moving indexes to another filegroup trouble...

I am trying to place all the clustered indexes to de data filegroup of my database and de non clustered indexes to de index filegroup of my database.

But in some databases I cannot change the location in the storage tab of the index property window because it is greyed out. The strange thing is that in some other databases it is possible, and some other partially. I even found a table with two non clustered indexes, of which one was possible to change, and the other not! Changing the compatibility mode of the database does not help.

So I will not get too happy about this.

1) Does anyone know why this behaviour is?
2) Does anyone has a script or tool (preferably free) which I can use to automate this task?

This all is in SQL Server 2005 Developer and using Management Studio.

Thanks in advance!

You can move an index by using the DROP_EXISTING clause.

Code Snippet

CREATE TABLE myTable

(ID int, NC varchar(3))

GO

CREATE CLUSTERED INDEX myCIX ON myTable (ID)

GO

CREATE NONCLUSTERED INDEX myIX ON myTable (NC)

GO

CREATE CLUSTERED INDEX myCIX ON myTable (ID)

WITH (DROP_EXISTING=ON) ON [OtherFG]

GO

CREATE NONCLUSTERED INDEX myIX ON myTable (NC)

WITH (DROP_EXISTING=ON) ON [OtherFG]

GO

sp_help myTable

DROP TABLE myTable

GO

For constraints check out the MOVE TO clause for the DROP constraint statement.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||See this http://www.mssqltips.com/tip.asp?tip=1112 too|||

Thanks, but wat I wanted to know, if somewone has an script that looks for all the indexes and creates move scripts or moves the indexes itself. I already knew how to do it, but with a database with 50-100 tables, handlabour is not cheap.