renaming a table

I know you can rename a table using sp_rename, but what I'm not sure about i
s
how to specify which database.
I'm doing this in VB (or in C#, it doesn't matter).
I have a connection to the SQL Server.
I can Exec sp_rename and give it oldname and newname for the table, but how
am I making sure it's for a table within a particular database?Try:
exec MyDB.dbo.sp_rename 'MyTable', 'MyNewTable'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
news:7E260686-7AA8-49AF-A674-7F2073B35ABC@.microsoft.com...
>I know you can rename a table using sp_rename, but what I'm not sure about
>is
> how to specify which database.
> I'm doing this in VB (or in C#, it doesn't matter).
> I have a connection to the SQL Server.
> I can Exec sp_rename and give it oldname and newname for the table, but
> how
> am I making sure it's for a table within a particular database?|||Les,
The activity will take place in the database you are connected to. Client
interfaces allow you to specify the database (catalog) you are connected to.
Or, you could achieve this with T-SQL:
USE database_name;
EXEC sp_rename ...
Unfortunately, sp_rename doesn't allow you to specify the database name,
rather it will run in the context of the "current" database.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
news:7E260686-7AA8-49AF-A674-7F2073B35ABC@.microsoft.com...
>I know you can rename a table using sp_rename, but what I'm not sure about
>is
> how to specify which database.
> I'm doing this in VB (or in C#, it doesn't matter).
> I have a connection to the SQL Server.
> I can Exec sp_rename and give it oldname and newname for the table, but
> how
> am I making sure it's for a table within a particular database?

Renaming a database

I've set my database to single user mode,and run:
sp_rename 'oldname' 'newname'
which works just fine.
Now I want to rename the oldname_data.mdf to
newname_data.mdf
To do that, I'm trying to detach the database, then go to
Explorer, rename the file and then reattach the database.
sp_detach_db 'newname'
returns an error:
Server: Msg 3702, Level 16, State 1, Line 1
Cannot drop the database 'newname' because it is currently
in use.
EXEC SQL DISCONNECT newname <or> 'newname'
returns an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'newname'.
How can I determine what has the database "in use" and
break that connection? (I'm on a 7.0 box in this case)
TIA MikeFirst open a query analyzer and connect to the SQL Server
in question. Then run this command:
SP_HOW2
search in the result and find any spid that currently
connected to the database and use this command ti kill
those users:
KILL <spid number>
That sould resolve your problem.
This posting is provided "AS IS" with no warranties, and
confers no rights.
http://www.microsoft.com/info/cpyright.htm
>--Original Message--
>I've set my database to single user mode,and run:
>sp_rename 'oldname' 'newname'
>which works just fine.
>Now I want to rename the oldname_data.mdf to
>newname_data.mdf
>To do that, I'm trying to detach the database, then go to
>Explorer, rename the file and then reattach the database.
>sp_detach_db 'newname'
>returns an error:
>Server: Msg 3702, Level 16, State 1, Line 1
>Cannot drop the database 'newname' because it is
currently
>in use.
>EXEC SQL DISCONNECT newname <or> 'newname'
>returns an error:
>Server: Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near 'newname'.
>How can I determine what has the database "in use" and
>break that connection? (I'm on a 7.0 box in this case)
>TIA Mike
>.
>|||I guess I'm not the only one with typo's today:) I think
that should be SP_WHO2
Vern
>--Original Message--
>First open a query analyzer and connect to the SQL Server
>in question. Then run this command:
>SP_HOW2
>search in the result and find any spid that currently
>connected to the database and use this command ti kill
>those users:
>KILL <spid number>
>That sould resolve your problem.
>This posting is provided "AS IS" with no warranties, and
>confers no rights.
>http://www.microsoft.com/info/cpyright.htm
>
>>--Original Message--
>>I've set my database to single user mode,and run:
>>sp_rename 'oldname' 'newname'
>>which works just fine.
>>Now I want to rename the oldname_data.mdf to
>>newname_data.mdf
>>To do that, I'm trying to detach the database, then go
to
>>Explorer, rename the file and then reattach the database.
>>sp_detach_db 'newname'
>>returns an error:
>>Server: Msg 3702, Level 16, State 1, Line 1
>>Cannot drop the database 'newname' because it is
>currently
>>in use.
>>EXEC SQL DISCONNECT newname <or> 'newname'
>>returns an error:
>>Server: Msg 170, Level 15, State 1, Line 1
>>Line 1: Incorrect syntax near 'newname'.
>>How can I determine what has the database "in use" and
>>break that connection? (I'm on a 7.0 box in this case)
>>TIA Mike
>>.
>.
>|||That is correct ;-) it is SP_WHO2.
Thanks for correction.
This posting is provided "AS IS" with no warranties, and
confers no rights.
http://www.microsoft.com/info/cpyright.htm
>--Original Message--
>I guess I'm not the only one with typo's today:) I think
>that should be SP_WHO2
>Vern
>>--Original Message--
>>First open a query analyzer and connect to the SQL
Server
>>in question. Then run this command:
>>SP_HOW2
>>search in the result and find any spid that currently
>>connected to the database and use this command ti kill
>>those users:
>>KILL <spid number>
>>That sould resolve your problem.
>>This posting is provided "AS IS" with no warranties, and
>>confers no rights.
>>http://www.microsoft.com/info/cpyright.htm
>>
>>--Original Message--
>>I've set my database to single user mode,and run:
>>sp_rename 'oldname' 'newname'
>>which works just fine.
>>Now I want to rename the oldname_data.mdf to
>>newname_data.mdf
>>To do that, I'm trying to detach the database, then go
>to
>>Explorer, rename the file and then reattach the
database.
>>sp_detach_db 'newname'
>>returns an error:
>>Server: Msg 3702, Level 16, State 1, Line 1
>>Cannot drop the database 'newname' because it is
>>currently
>>in use.
>>EXEC SQL DISCONNECT newname <or> 'newname'
>>returns an error:
>>Server: Msg 170, Level 15, State 1, Line 1
>>Line 1: Incorrect syntax near 'newname'.
>>How can I determine what has the database "in use" and
>>break that connection? (I'm on a 7.0 box in this case)
>>TIA Mike
>>.
>>.
>.
>

Renaming a database

I've renamed a database using the sp_rename but now I
notice that the mdf file is still named oldDBname.mdf.
Is there a way to rename that file to newDBname.mdf?Renaming the database does not change the physical name of the file. To do that you will have to
use system stored procedure sp_detach_db and sp_attach_db.
An approximate sequence of the steps to be taken would be something like this:
detach the db using sp_detach_db
rename the physical files.
use sp_attach_db stored procedure to attach these rename files.
See more help on this in BOL.
--
- Vishal
"mark" <mhoyt@.affiliatedhealth.org> wrote in message news:02c601c38dc5$c37c8fe0$a301280a@.phx.gbl...
> I've renamed a database using the sp_rename but now I
> notice that the mdf file is still named oldDBname.mdf.
> Is there a way to rename that file to newDBname.mdf?|||Try backup and restore.
/Stefan
"mark" <mhoyt@.affiliatedhealth.org> skrev i meddelandet
news:02c601c38dc5$c37c8fe0$a301280a@.phx.gbl...
> I've renamed a database using the sp_rename but now I
> notice that the mdf file is still named oldDBname.mdf.
> Is there a way to rename that file to newDBname.mdf?|||Vishal:
How about if I want to change the logical name also?
Thanks
"Vishal Parkar" <_vgparkar@.hotmail.com> wrote in message
news:uyhhcdcjDHA.2364@.TK2MSFTNGP11.phx.gbl...
> Renaming the database does not change the physical name of the file. To do
that you will have to
> use system stored procedure sp_detach_db and sp_attach_db.
> An approximate sequence of the steps to be taken would be something like
this:
> detach the db using sp_detach_db
> rename the physical files.
> use sp_attach_db stored procedure to attach these rename files.
> See more help on this in BOL.
> --
> - Vishal
> "mark" <mhoyt@.affiliatedhealth.org> wrote in message
news:02c601c38dc5$c37c8fe0$a301280a@.phx.gbl...
> > I've renamed a database using the sp_rename but now I
> > notice that the mdf file is still named oldDBname.mdf.
> >
> > Is there a way to rename that file to newDBname.mdf?
>|||In SQL 2000, you can use ALTER DATABASE command to change the logical name of the file.
Ex:
alter database northwind modify file
(name = 'northwind', newname = 'northwind_new')
- Vishal|||great.
"Vishal Parkar" <_vgparkar@.hotmail.com> wrote in message
news:%23mbcjrcjDHA.2268@.TK2MSFTNGP12.phx.gbl...
> In SQL 2000, you can use ALTER DATABASE command to change the logical
name of the file.
> Ex:
> alter database northwind modify file
> (name = 'northwind', newname = 'northwind_new')
>
> --
> - Vishal
>|||and how we do this using Enterprise Manager. I openend the properties of
databse but I didn't find any option to rename the logical filename ?
"Vishal Parkar" <_vgparkar@.hotmail.com> wrote in message
news:%23mbcjrcjDHA.2268@.TK2MSFTNGP12.phx.gbl...
> In SQL 2000, you can use ALTER DATABASE command to change the logical
name of the file.
> Ex:
> alter database northwind modify file
> (name = 'northwind', newname = 'northwind_new')
>
> --
> - Vishal
>|||you cant do this through enterprise manager.
--
- Vishal
"Sender" <user@.domain.com> wrote in message news:OcNl6ycjDHA.2268@.TK2MSFTNGP12.phx.gbl...
> and how we do this using Enterprise Manager. I openend the properties of
> databse but I didn't find any option to rename the logical filename ?
>
> "Vishal Parkar" <_vgparkar@.hotmail.com> wrote in message
> news:%23mbcjrcjDHA.2268@.TK2MSFTNGP12.phx.gbl...
> > In SQL 2000, you can use ALTER DATABASE command to change the logical
> name of the file.
> > Ex:
> > alter database northwind modify file
> > (name = 'northwind', newname = 'northwind_new')
> >
> >
> > --
> > - Vishal
> >
> >
>

renamed table [need to quickly update 200 SPs]

Hi
I have had to rename one of my tables (sp_Rename []) I now need to update
all my stored procedures, I want to some how use syscomments (maybe) and
loop through my SPs using REPLACE command to change the references to the
old table name
Any help / advise appreciated
LukeHi,
One solution is, you can generate a script with all the SPs available.
Open some text editor and use Replace All option giving your old and new
File Name.
Run the script again in Query Analyser
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Luke Ward" wrote:

> Hi
> I have had to rename one of my tables (sp_Rename []) I now need to update
> all my stored procedures, I want to some how use syscomments (maybe) and
> loop through my SPs using REPLACE command to change the references to the
> old table name
> Any help / advise appreciated
> Luke
>
>|||Don't fiddle with system tables ever! I would hope you have some
sort of code mgmt (ie VSS) in place where your master copies of
all procs are kept. That's where your changes should be made, tested
against a copy of your production data, and then applied to the
production database when you're satisfied with the results.
I think your best bet is to manually change the table name within
the procs. If you know a scripting language (like Perl), you could
write a script to blow through the procs and make the change and then
compile them back into the database.. That's what I would do.
"Luke Ward" <lukeward@.campbelluk.com> wrote in message
news:e6GC%23xsWFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi
> I have had to rename one of my tables (sp_Rename []) I now need to update
> all my stored procedures, I want to some how use syscomments (maybe) and
> loop through my SPs using REPLACE command to change the references to the
> old table name
> Any help / advise appreciated
> Luke
>

rename column with check constraints

I want to change the name of a column. I tried to use sp_rename but i get the message:
"Object 'table2.TelNoMob' cannot be renamed because the object participates in enforced dependencies."

I think this is because the specific column has check constraint.

Is there a way of changing the column's name?You will have to drop the constraint and add it it again afterwards.

This will give you the constraint name to drop

select object_name(constid) from sysconstraints where id = object_id('tblname')
and colid = (select colid from syscolumns where id = object_id('tblname') and name = 'colname')

Rename a primary key column

hi i am trying to rename a primary using sp_rename. but it keeps failing with
message that the column is involved in dependencies. but i have already
dropped all constraints on this table! any help please
On Tue, 31 May 2005 18:04:02 -0700, "sql guy" <sql
guy@.discussions.microsoft.com> wrote:

>hi i am trying to rename a primary using sp_rename. but it keeps failing with
>message that the column is involved in dependencies. but i have already
>dropped all constraints on this table! any help please
Hi sql guy,
Are there any foreign key constraints on other table that reference the
primary key column you're trying to rename?
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||hi hugo, yes there are loads of them. however, i finally resolved it all
yesterday. thanks for the reply.
cheers
"Hugo Kornelis" wrote:

> On Tue, 31 May 2005 18:04:02 -0700, "sql guy" <sql
> guy@.discussions.microsoft.com> wrote:
>
> Hi sql guy,
> Are there any foreign key constraints on other table that reference the
> primary key column you're trying to rename?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>