2005 Schemas

Does anyone know if you can rename a schema in 2k5?
I checked out sp_rename, but it doesn't appear to be a supported object type
from the documentation. Unless I'm reading it wrong.
Thanks."sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:C14095AC-5051-4256-ADBD-C330B819FAC3@.microsoft.com...
> Does anyone know if you can rename a schema in 2k5?
> I checked out sp_rename, but it doesn't appear to be a supported object
> type
> from the documentation. Unless I'm reading it wrong.
>
You cannot rename a schema.
David|||Cool, thanks.
"David Browne" wrote:
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:C14095AC-5051-4256-ADBD-C330B819FAC3@.microsoft.com...
> > Does anyone know if you can rename a schema in 2k5?
> > I checked out sp_rename, but it doesn't appear to be a supported object
> > type
> > from the documentation. Unless I'm reading it wrong.
> >
>
> You cannot rename a schema.
> David
>
>|||"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:C14095AC-5051-4256-ADBD-C330B819FAC3@.microsoft.com...
> Does anyone know if you can rename a schema in 2k5?
> I checked out sp_rename, but it doesn't appear to be a supported object
> type
> from the documentation. Unless I'm reading it wrong.
> Thanks.
>
I think you'll have to create a new schema then move existing objects to it
using ALTER SCHEMA:
ALTER SCHEMA new_schema_name TRANSFER old_schema_name.object_name
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

2005 Schemas

Does anyone know if you can rename a schema in 2k5?
I checked out sp_rename, but it doesn't appear to be a supported object type
from the documentation. Unless I'm reading it wrong.
Thanks.
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:C14095AC-5051-4256-ADBD-C330B819FAC3@.microsoft.com...
> Does anyone know if you can rename a schema in 2k5?
> I checked out sp_rename, but it doesn't appear to be a supported object
> type
> from the documentation. Unless I'm reading it wrong.
>
You cannot rename a schema.
David
|||Cool, thanks.
"David Browne" wrote:

> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:C14095AC-5051-4256-ADBD-C330B819FAC3@.microsoft.com...
>
> You cannot rename a schema.
> David
>
>
|||"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:C14095AC-5051-4256-ADBD-C330B819FAC3@.microsoft.com...
> Does anyone know if you can rename a schema in 2k5?
> I checked out sp_rename, but it doesn't appear to be a supported object
> type
> from the documentation. Unless I'm reading it wrong.
> Thanks.
>
I think you'll have to create a new schema then move existing objects to it
using ALTER SCHEMA:
ALTER SCHEMA new_schema_name TRANSFER old_schema_name.object_name
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

2005 Schemas

Does anyone know if you can rename a schema in 2k5?
I checked out sp_rename, but it doesn't appear to be a supported object type
from the documentation. Unless I'm reading it wrong.
Thanks."sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:C14095AC-5051-4256-ADBD-C330B819FAC3@.microsoft.com...
> Does anyone know if you can rename a schema in 2k5?
> I checked out sp_rename, but it doesn't appear to be a supported object
> type
> from the documentation. Unless I'm reading it wrong.
>
You cannot rename a schema.
David|||Cool, thanks.
"David Browne" wrote:

> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:C14095AC-5051-4256-ADBD-C330B819FAC3@.microsoft.com...
>
> You cannot rename a schema.
> David
>
>|||"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:C14095AC-5051-4256-ADBD-C330B819FAC3@.microsoft.com...
> Does anyone know if you can rename a schema in 2k5?
> I checked out sp_rename, but it doesn't appear to be a supported object
> type
> from the documentation. Unless I'm reading it wrong.
> Thanks.
>
I think you'll have to create a new schema then move existing objects to it
using ALTER SCHEMA:
ALTER SCHEMA new_schema_name TRANSFER old_schema_name.object_name
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

2005 breaks sp_rename for constraints?

BooksOnline indicates that sp_rename can still be used to rename constraints such as primary keys, foreign keys, defaults, etc. in SQL Server 2005. However, when I try to do so I get this:

"Either the parameter @.objname is ambiguous or the claimed @.objtype (object) is wrong."

After looking at the code for sp_rename, it looks like this problem might be related to another bit of apparent misbehavior on 2005's part: when I execute "select object_name()" with the Id number of a constraint, it returns the name, but when I execute "select object_id()" with the name of that same constraint, it returns null. Why is that?

Thanks,

Ron

Did you specify the schema ? make sure that Object_id is only valid for the current context, the database you are currently connected with.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

It worked for me on RTM Express Edition. Can you post the code you used/reproduce it? Here is my code:

create table test
(
testId int constraint pktest primary key
)
go
select object_id('pktest')
select object_name(object_id('pktest'))
go
/*
--
613577224

pktest
*/
go
sp_rename 'pktest','newPkTest'
go
select object_id('pktest')
select object_name(object_id('pktest'))

select object_id('newPkTest')
select object_name(object_id('newPktest'))
go
/*

--
NULL

--
NULL

--
613577224

--
newPkTest
*/

|||

Sorry, I thought I had deleted this one. Yes, I had not specified the schema of the constraint. Doing so helped a lot!

Thanks,

Ron