February 7, 2011 at 10:51 am
Hi,
I have to cleanup my test database. So, I want to remove all the partitions from the all the tables in the database. Is there a way to do this?
Thank you,
VG
February 7, 2011 at 11:19 am
Not in a single command, if that's what you mean. I guess you could probably code a dynamic script to do it, but unless you're planning on doing this lots of times, it's probably easiest to just script it out by hand.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 7, 2011 at 11:52 am
I'm not sure what you mean by removing all partitions. Do you want to drop the tables, or just rebuild them to make them non-partitioned?
In either case, you can use a cursor to iterate over all tables in your database:
declare @name sysname
declare cur cursor for select name from [database].sys.tables
open cur
fetch next from cur into @name
while @@fetch_status = 0
begin
print 'Processing table ' + @name
-- Run your cleanup code
fetch next from cur into @name
end
close cur
deallocate cur
go
February 7, 2011 at 12:52 pm
for the test table, I would drop the table and recreate everything from a script.
February 7, 2011 at 12:55 pm
I was think of dropping and recreating the tables too..
Thanks for all of your suggestions and help!
-VG
February 7, 2011 at 2:24 pm
HI,
I created a drop and create table scripts with the table partitioning. But, this didn't remove the partition at all..
I see all the partitions (around 22) and I can’t drop partition schema and function either, getting error…. Is that means that, I have to manually merge all the partitions?
I have around 30 tables, with each one having 22 partitions…I want to remove all the partitions and the data from these tables. Any pointers please..
Thank you,
VG
February 7, 2011 at 3:09 pm
my first instinct would be to confirm that the objects that appear in this list need to be scripted to move their clustered index to the default partition(or dropped), and then after that is done , you can delete the partitions, right?
select object_name(object_id) As ObjName,* from sys.partitions where partition_number <> 1
Lowell
February 7, 2011 at 3:48 pm
You will get error obviously because
partition function is used by table and scheme is uses of function
follow this link for detail all steps here
So the correct way to drop or rebuild the partitioning is:
1. Remove the partitioning:
2. Drop the current clustered index of the Partitioned Table.
3. Create a new clustered index that will not use the partition scheme.
4. DROP the PARTITION SCHEMA.
5. Then DROP the PARTITION FUNCTION.
This an approach to removing the partitioning.
February 7, 2011 at 4:29 pm
I think, now we are back to square 1..
Remove the partitioning:
My questiopn was how to remove the partition? Do I have to do it manually? Since I have 22 partitions on each table?
I was suggested to do, drop and recreate the table. But, this didn't remove the partitions?
February 7, 2011 at 4:48 pm
Steps you need to do for each table as far as I know
drop clustered index on the table
create dummy clustred index
the new clustred index won't use your partion scheme so you can drop it
then drop partition function
then also drop the dummy clustered index thats it
you should be good
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply