Confused by sp_rename.

I am confused by the rules for the sp_rename stored proc and it's
interaction with other statements . It seems that is provide owner.name
for the second parameter (instead of just name), the SELECT statement
afterwards simply will tell me that the table does not exist. Am I
missing something really simple?
For instance, the following works fine:
sp_rename N'[dbo].[tmp_sfx_tbl_003_01]', N'tbl_003_01'
select * from tbl_003_01
However, the statements below do not work:
sp_rename 'tmp_sfx_tbl_003_01', '[dbo].[tbl_003_01]'
select * from tbl_003_01 --returns Invalid object name 'dbo.tbl_003_01'.
Thanks
Sp_rename does not allow for owner/schema qualified naming for new name. This would allow you to
change owner/schema with sp_rename, so it will interpret that part as the new name. What you did was
to rename the table to:
[dbo].[a1]
Above is the name without any delimiters or owner/schema. So, to use it, having delimiters and
owner/schema, you use:
[dbo].[[dbo]].[a1]]]
To see what happend, try below:
Use tempdb
CREATE TABLE dbo.a(c1 int)
GO
EXEC sp_rename a, '[dbo].[a1]'
GO
SELECT name FROM sysobjects WHERE name LIKE '%a1%'
SELECT * FROM [dbo].[[dbo]].[a1]]]
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Rizzo" <none@.none.net> wrote in message news:%232VAclflHHA.4188@.TK2MSFTNGP02.phx.gbl...
>I am confused by the rules for the sp_rename stored proc and it's interaction with other statements
>. It seems that is provide owner.name for the second parameter (instead of just name), the SELECT
>statement afterwards simply will tell me that the table does not exist. Am I missing something
>really simple?
> For instance, the following works fine:
> sp_rename N'[dbo].[tmp_sfx_tbl_003_01]', N'tbl_003_01'
> select * from tbl_003_01
> However, the statements below do not work:
> sp_rename 'tmp_sfx_tbl_003_01', '[dbo].[tbl_003_01]'
> select * from tbl_003_01 --returns Invalid object name 'dbo.tbl_003_01'.
>
> Thanks
|||Tibor Karaszi wrote:
> Sp_rename does not allow for owner/schema qualified naming for new name.
> This would allow you to change owner/schema with sp_rename, so it will
> interpret that part as the new name. What you did was to rename the
> table to:
> [dbo].[a1]
> Above is the name without any delimiters or owner/schema. So, to use it,
> having delimiters and owner/schema, you use:
> [dbo].[[dbo]].[a1]]]
>
> To see what happend, try below:
>
> Use tempdb
> CREATE TABLE dbo.a(c1 int)
> GO
> EXEC sp_rename a, '[dbo].[a1]'
> GO
> SELECT name FROM sysobjects WHERE name LIKE '%a1%'
> SELECT * FROM [dbo].[[dbo]].[a1]]]
So how would I assign ownership as a part of the rename? Say I want to
rename dbo.temp_a1 to rizzo.a1? Or rizzo.temp_a1 to rizzo.a1?
Thanks
|||Look up "alter schema ... transfer" in BOL.
"Frank Rizzo" <none@.none.com> wrote in message
news:ejlnomklHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Tibor Karaszi wrote:
>
> So how would I assign ownership as a part of the rename? Say I want to
> rename dbo.temp_a1 to rizzo.a1? Or rizzo.temp_a1 to rizzo.a1?
> Thanks
|||> So how would I assign ownership as a part of the rename? Say I want to rename dbo.temp_a1 to
> rizzo.a1? Or rizzo.temp_a1 to rizzo.a1?
You don't. You do it in two separate operations.
Chancing object owner in 2000 is done using sp_changeobjectowner. In 2005, the command depends on
whether you want to change schema or owner.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Rizzo" <none@.none.com> wrote in message news:ejlnomklHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Tibor Karaszi wrote:
>
> So how would I assign ownership as a part of the rename? Say I want to rename dbo.temp_a1 to
> rizzo.a1? Or rizzo.temp_a1 to rizzo.a1?
> Thanks