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/sqlserver/bb428874.aspx
"Praveen" <apveen@.gmail.com> wrote in message
news:1186027495.971909.295310@.19g2000hsx.googlegro ups.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
>

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

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/sqlserver/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
>

Drop User and schema

How do I determine the schema own by a user, remove them and drop the user?
I am trying to drop a user and I get the following error message:
The database principal owns a schema in the database, and cannot be dropped.
Thanks.
Please disregard. I changed the schema owner under the schema object of the
database.
"Emma" wrote:

> How do I determine the schema own by a user, remove them and drop the user?
> I am trying to drop a user and I get the following error message:
> The database principal owns a schema in the database, and cannot be dropped.
> Thanks.

Drop User and schema

How do I determine the schema own by a user, remove them and drop the user?
I am trying to drop a user and I get the following error message:
The database principal owns a schema in the database, and cannot be dropped.
Thanks.Please disregard. I changed the schema owner under the schema object of the
database.
"Emma" wrote:
> How do I determine the schema own by a user, remove them and drop the user?
> I am trying to drop a user and I get the following error message:
> The database principal owns a schema in the database, and cannot be dropped.
> Thanks.sql

Drop User and schema

How do I determine the schema own by a user, remove them and drop the user?
I am trying to drop a user and I get the following error message:
The database principal owns a schema in the database, and cannot be dropped.
Thanks.Please disregard. I changed the schema owner under the schema object of the
database.
"Emma" wrote:

> How do I determine the schema own by a user, remove them and drop the user
?
> I am trying to drop a user and I get the following error message:
> The database principal owns a schema in the database, and cannot be droppe
d.
> Thanks.

drop schema and its objects

I want to be able to drop a schema and all its objects if they exist. Can someone help me with such a stored procedure. I see the sql server does not allow dropping schema directly if it contains some objects.

It is not always true. if you use indexed views, foreign keys then you are correct.

You can use sp_depends 'Your table name' to find all the dependent views/sps & others..

|||

I think I did not explain my question clearly.

I am using :

Code Snippet

DROP SCHEMA [new]

Msg 3729, Level 16, State 1, Line 1

Cannot drop schema 'new' because it is being referenced by object 'cattr'.

I want to be able to drop the schema, even if some objects reference it. If it is mandatory to first drop all objects that the schema contains, then how can I programmitically delete all such objects and then delete the schema ?

So given a schema name the stored procedure should be able to drop the schema.

|||

Is there any way to do the above mentioned.

|||

You could use following statement for determining objects in schema and there types:

Code Snippet

select obj.type, obj.name

from sys.objects obj join sys.schemas s on (s.schema_id=obj.schema_id)

where s.name='<SCHEMA NAME>'

Then, you could use cursor for calling needed DROP statements

|||

I think there is one problem with the above approach. We need to drop tables in a schema in a particular order as there may be referencences, foreign key relationships etc.

How do I determine the order ?

|||

You could delete constrains at first, then delete tables.

Also you could include each DROP statement in TRY/CATCH block and when some DROP fail analyze error message

|||

Or even try following:

Code Snippet

declare @.cnt int

select @.cnt=count(*)

from sys.objects obj join sys.schemas s on (s.schema_id=obj.schema_id)

where s.name='<you schema>'

--iterate while all objects deleted

while @.cnt >0

Begin

--Drop objects with cursor

--Each drop statemnt include into TRY/CATCH block, but ignore error

--New objects count

select @.cnt=count(*)

from sys.objects obj join sys.schemas s on (s.schema_id=obj.schema_id)

where s.name='<you schema>'

end

|||

>> obj.type, obj.name

How do I construct the drop statement from these ?

Say I got 'U' as type and 'cattr' as table name, I need the statement "drop table cattr"

But how to get the mapping between type 'U' and what it represents 'table'

or do I have write explit if else statements like

if type = 'U'

drop table

elsif type = 'P'

drop procedure....

But that's going to be a long list.

|||

I am not sure as to why it is so difficult to drop a schema.... especially when someone has permissions to do it. When we can drop a table eventhough it contains some data, why can't we drop a schema even if it contains some objects ?

And How do I construct drop statements for each object , the problem which I described above ?

|||

You could use dynamic sql for constructing drop statements.

Use CASE statement for constructing varchar variable:

@.dropQuery = 'DROP '+<case statements that returns TABLE, CONSTRAINT etc>+' '+obj.name

Then: EXECUTE(@.dropQuery)

DROP DATABASE problem

I have an application (VS2005, C#) that creates one or more SQL Server
Express (2005) databases and populates them with a schema and some
initial data. This all works.
From time to time there is a legitimate runtime issue that means I
should remove one of the newly created databases. The problem is that
the DROP DATABASE XXX command, which I execute from a new connection via
an admin login on the master database, fails with the error "Cannot drop
database "XXX" because it is currently in use.".
I have checked with "sp_who" and there are no active connections to the
database, but there are a couple of 'sleeping' connections, which I
presume belong to the connection pool.
If I wait for some period of time, the sleeping connections disappear
and the DROP DATABASE command then succeeds.
What is the simplest mechanism for permitting me to drop the database
without waiting for the connection pool to expire on its own?
-ken
Hi Ken,
ALTER DATABASE SomeDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE SomeDatabase
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||I actually found another method that works as well -- I simply use the
SqlConnection.Clear(specificConnection) to remove the entries from the
pool, and this seems to work.
-ken
Jens wrote:
> Hi Ken,
> ALTER DATABASE SomeDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> GO
> DROP DATABASE SomeDatabase
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>