Droping Partition Schema SQL 2005

I have a Partition schema PS1 mapped to a table name 'A'. And there
are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
the Drop the Partition Schema. Could you help me to drop the Partition
Schema please?.
Thanks>I have a Partition schema PS1 mapped to a table name 'A'. And there
> are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
> the Drop the Partition Schema. Could you help me to drop the Partition
> Schema please?.
You can't drop the partition scheme while the table is still using it, so
the solution is to move the table to a filegroup or another partition
scheme. How you do that depends on whether you have indexes on the table or
not. The following information gives you some alternative methods depending
on your situation.
If you do not have a clustered index on the table.
1. Create a clustered index on the table in the statement specify a
filegroup (or other partition scheme). This places the data in the specified
filegroup by moving the data from the table to the leaf level of the
clustered index. Note that if you have nonclustered indexes on the table,
you should drop these before creating the clustered index. You can recreate
the nonclustered indexes after you have created the clustered index.
The syntax is
CREATE CLUSTERED INDEX index_name ON table_name(column_name)
ON filegroup_name -- for example, ON DEFAULT if you want to use the
default filegroup
2. Drop the clustered index if you don't want to keep it and the data will
remain in the filegroup.
3. Recreate any nonclustered indexes.
If you already have a clustered index on the table.
1. Drop any nonclustered indexes. You can recreate them later.
2. Use the DROP INDEX statement with the MOVE TO clause.
The syntax is
DROP INDEX index_name ON table_name
WITH (MOVE TO filegroup_name);
3. Recreate the clustered index and then any nonclustered indexes on the
table.
An alternative to using DROP INDEX is the CREATE INDEX WITH DROP_EXISTING
statement. The index name and column name(s) must be the same as the
existing index.
CREATE CLUSTERED INDEX index_name ON table_name(column_name)
WITH (DROP_EXISTING = ON)
ON filegroup_name
Once you have the table moved off of the partition, you can drop the
partition schemee using DROP PARTITION SCHEME partition_scheme_name
Hope that gets you going.
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/...r/bb428874.aspx
"Praveen" <apveen@.gmail.com> wrote in message
news:1186027495.971909.295310@.19g2000hsx.googlegroups.com...
>I have a Partition schema PS1 mapped to a table name 'A'. And there
> are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
> the Drop the Partition Schema. Could you help me to drop the Partition
> Schema please?.
> Thanks
>sql