MS SQL Server - Rename a table sql 2k

Author

Rename a table sql 2k

mecn

2006-02-28, 8:23 pm

HI,
I am trying to rename a table, it gives me this error,
Object 'Test' cannot be renamed because the object participates in enforced
dependencies.

How do I rename a table then create a new table with the original name.

thanks,

Rick Sawtell

2006-02-28, 8:23 pm

"mecn" <mecn2002@yahoo.com> wrote in message
news:uC6yio6OGHA.3100@TK2MSFTNGP11.phx.gbl...
> HI,
> I am trying to rename a table, it gives me this error,
> Object 'Test' cannot be renamed because the object participates in
> enforced dependencies.
>
> How do I rename a table then create a new table with the original name.
>
> thanks,
>

It looks like you have some FK constraints that reference that table, or
some SCHEMABINDING constraints somewhere. You will have to drop those first
before you can rename the table.

You could use ALTER TABLE commands to add new columns to your table...

Rick Sawtell
MCT, MCSD, MCDBA

mecn

2006-02-28, 8:23 pm

Thanks, Rick.

How do I drop these dependencies(I just want to rename it ---lots of
records) and readd depedencies on the new table.

Thanks again

"Rick Sawtell" <Quickening@msn.com> wrote in message
news:ezvV716OGHA.1696@TK2MSFTNGP14.phx.gbl...
>
> "mecn" <mecn2002@yahoo.com> wrote in message
> news:uC6yio6OGHA.3100@TK2MSFTNGP11.phx.gbl...
>
> It looks like you have some FK constraints that reference that table, or
> some SCHEMABINDING constraints somewhere. You will have to drop those
> first before you can rename the table.
>
> You could use ALTER TABLE commands to add new columns to your table...
>
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>

Tibor Karaszi

2006-02-28, 8:23 pm

You can do ALTER of the views and functions that has schemabinding against your table to recreate
the objects without schemabinding, make your table changes and then re-create your functions and
views again, with schemabinding.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/

"mecn" <mecn2002@yahoo.com> wrote in message news:uurS1%236OGHA.344@TK2MSFTNGP11.phx.gbl...
> Thanks, Rick.
>
> How do I drop these dependencies(I just want to rename it ---lots of records) and readd
> depedencies on the new table.
>
> Thanks again
>
> "Rick Sawtell" <Quickening@msn.com> wrote in message news:ezvV716OGHA.1696@TK2MSFTNGP14.phx.gbl...
>
>

mecn

2006-02-28, 8:23 pm

Thanks,
There is no quick way to drop the dependencies? That table has about 15 - 20
dependecies. I need to alter all 15-20 objects before I could rename and
alter them again to a new table?

"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
message news:O2cd0G7OGHA.2624@TK2MSFTNGP12.phx.gbl...
> You can do ALTER of the views and functions that has schemabinding against
> your table to recreate the objects without schemabinding, make your table
> changes and then re-create your functions and views again, with
> schemabinding.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
> Blog: http:// solidqualitylearning
.com/blogs/tibor/
>
>
> "mecn" <mecn2002@yahoo.com> wrote in message
> news:uurS1%236OGHA.344@TK2MSFTNGP11.phx.gbl...
>

Tibor Karaszi

2006-02-28, 8:23 pm

I guess you could write some code which does this, using the DMO or SMO API (depending on SQL Server
version). http://www.karaszi.com/SQLServer/ i...ript
.asp

But you still have to modify these objects as you are about to do changes in the table (name), and
these objects need to be adjusted properly. If it is a one off, manual would be the fastest thing
(IMO). If you are about to do this regularly, I'd go with some DMO or SMO coding.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/

"mecn" <mecn2002@yahoo.com> wrote in message news:eNMM0O7OGHA.3944@tk2msftngp13.phx.gbl...
> Thanks,
> There is no quick way to drop the dependencies? That table has about 15 - 20 dependecies. I need
> to alter all 15-20 objects before I could rename and alter them again to a new table?
>
>
> "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in message
> news:O2cd0G7OGHA.2624@TK2MSFTNGP12.phx.gbl...
>
>

mecn

2006-02-28, 8:23 pm

Tibor,

Thanks a lot.

"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
message news:u8T68l7OGHA.1460@TK2MSFTNGP10.phx.gbl...
>I guess you could write some code which does this, using the DMO or SMO API
>(depending on SQL Server version).
>http://www.karaszi.com/SQLServer/ i...ript
.asp
>
> But you still have to modify these objects as you are about to do changes
> in the table (name), and these objects need to be adjusted properly. If it
> is a one off, manual would be the fastest thing (IMO). If you are about to
> do this regularly, I'd go with some DMO or SMO coding.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
> Blog: http:// solidqualitylearning
.com/blogs/tibor/
>
>
> "mecn" <mecn2002@yahoo.com> wrote in message
> news:eNMM0O7OGHA.3944@tk2msftngp13.phx.gbl...
>

Sponsored Links