I need to rename a table.

[fergman]

It appears there is no mssql query to rename a table (yet oracle and mysql both have one), Here is what I have, and I need to write a query to rename a table. It's a bit messy at the moment, but basically I've got a customer table and my user entered the name wrong and added a bunch of data anyway. I want to set it up so she can rename it from the website. It appears there is no option short of creating a new table copying the data and deleting the old table, which seems like a horrible waste of memory. (yes I have to use tables) There is also the companyList database which just contains contact info for each company. I don't need to worry about this one.

Is there any query that would work at all for this?

<%
dim delete, customers, maincontact, phonenumber, altphone, email, fax, catalog
companyName = request.form(&quot;companyName&quot
delete = request.form(&quot;delete&quot
catalog = request.form(&quot;catalog&quot

if delete = &quot;deleteYes&quot; then
Set Rs = Server.CreateObject(&quot;ADODB.Recordset&quot
SQL = &quot;DROP TABLE [&quot; & companyName & &quot;]&quot;
Rs.Open SQL, outsales, 1,3

set RS = nothing
set Rs = Server.CreateObject(&quot;ADODB.Recordset&quot
SQL = &quot;SELECT * from companyList WHERE customers LIKE '&quot;& companyName &&quot;'&quot;
Rs.Open SQL, outsales, 1,3

do while not rs.EOF
if Rs(&quot;customers&quot = companyName then Rs.Delete
rs.movenext
loop
set rs=nothing
response.redirect &quot;default.asp&quot;
else
customers = request.form(&quot;customers&quot
mainContact = request.form(&quot;mainContact&quot
phoneNumber = request.form(&quot;phoneNumber&quot
altPhone = request.form(&quot;altPhone&quot
email = request.form(&quot;email&quot
fax = request.form(&quot;fax&quot
catalog = request.form(&quot;catalog&quot

set rs=nothing
set Rs = Server.CreateObject(&quot;ADODB.Recordset&quot
SQL=&quot;SELECT * FROM companyList WHERE customers LIKE '&quot;& companyName &&quot;'&quot;
Rs.Open SQL, outsales, 1,3

'rs(&quot;customers&quot = customers
rs(&quot;phoneNumber&quot = phoneNumber
rs(&quot;altPhone&quot = altPhone
rs(&quot;mainContact&quot = mainContact
rs(&quot;email&quot = email
rs(&quot;fax&quot = fax
rs(&quot;catalog&quot = catalog
rs.update
response.redirect &quot;default.asp&quot;
end if

%>

 

[zhavic]

Ofcourse, there is one, system stored procedure
sp_rename

for example to rename 'table_1' to 'table_one' use:

EXECUTE sp_rename 'table_1', 'table_one'

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[fergman]

I don't understand how I would use that, would it be sql=&quot;EXECUTE sp_name 'table_1','table_one'&quot;
?

 

[zhavic]

I am not using ASP but I found this there thread333-610368 and I changed the name of sp and parametres:

Set Cmd = Server.CreateObject(&quot;ADODB.Command&quot
With Cmd
.ActiveConnection = strConn
.CommandText = &quot;sp_rename&quot; ' name of stored procedure
.CommandType = adCmdStoredProc
.parameters.refresh
.parameters.item(&quot;@objname&quot = &quot;table_1&quot; ' old table name
.parameters.item(&quot;@newname&quot = &quot;table_one&quot; ' new table name
Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
Set rs = .Execute

'clean up
Set rs = Nothing
Set Cmd = Nothing
End With


Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[SQLSister]

Renaming tables is not generally something you do not want users to be able to do. The reasons why this is a bad idea is that renaming breaks all dependencies. So the indexes no longer work, the foreign key relationships no longer work and the stored procedures and user interface code no longer work when they reference the table name.

You may run into permissions problems as I believe the only people who can rename a table are the sys admin and the table owner.