How do I rename a column?

SQL Server 
 
In SQL Server, we can use sp_rename. The format is: 
 

EXEC sp_rename      @objname = 'table_name.old_column_name',      @newname = 'new_column_name',      @objtype = 'COLUMN'

 
So, from ASP, use the following: 
 

<%       tbl = "table_name"      oldCol = "old_column_name"      newCol = "new_column_name"        cst = "Provider=SQLOLEDB.1;Data Source=<server/ip>;" & _           "initial catalog=<dbname>;network=DBMSSOCN;" & _           "user id=<uid>;password=<pwd>"       set conn = CreateObject("ADODB.Connection")       conn.open cst        sql = "EXEC sp_rename '" & tbl & "." & oldCol & "'," & _          "'" & newCol & "', 'COLUMN'"        conn.execute sql, , 129      conn.close: set conn = nothing  %>

 
One problem you might come across is when you misspell the column or table name, or execute the same rename command twice. You would see this error in SQL Server: 
 

Server: Msg 15248, Level 11, State 1, Procedure sp_rename, Line 163  Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.

 
Which translates in ASP to: 
 

Microsoft OLE DB Provider for SQL Server error '80040e14'   Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.

 
Another potential problem is that, in certain scenarios, the following warning can bubble up to ADO code: 
 

Caution: Changing any part of an object name could break scripts and stored procedures.  The COLUMN was renamed to '<new_column_name>'.

 
This is the warning that is presented in Query Analyzer when you run sp_rename manually. It is a fairly important warning because, while foreign keys, indexes and constraints directly related to the table will be updated, other objects that contain code referencing this column (like views, stored procedures, triggers, and user-defined functions) will break unless you retrofit them with the new names. 
 
This error won't come up in the native ASP error collection, but it will come up in the errors collection of the connection object (which is sometimes used to trap errors from SQL Server). So, ASP knows enough to not balk at this "error," but if your code is using the connection object's errors collection, you will need to know the difference. You can see the errors coming back, using the following code: 
 

<%      ' ... connection/variable code from above ...        sql = "EXEC sp_rename '" & tbl & "." & oldCol & "'," & _           "'" & newCol & "', 'COLUMN'"         conn.execute sql, , 129         for i = 0 to conn.errors.count-1          response.write conn.errors(i).nativeError & "<br>"          response.write conn.errors(i).description & "<p>"      next      conn.close: set conn = nothing   %>

 
Which returns the following to the browser: 
 

NativeError: 15477  Description: Caution: Changing any part of an object name could break scripts and stored procedures.    NativeError: 15338  Description: The COLUMN was renamed to 'realnumber'.

 
So, to prevent this warning from unnecessarily tripping up your error trapping code, you can just ignore those specific NativeError numbers: 
 

<%      ' ... connection/variable code from above ...        sql = "EXEC sp_rename '" & tbl & "." & oldCol & "'," & _           "'" & newCol & "', 'COLUMN'"         conn.execute sql, , 129         for i = 0 to conn.errors.count-1          e = conn.errors(i).nativeError          d = conn.errors(i).description          if e <> 15477 and e <> 15338 then              response.write e & ":" & d & "<p>"          end if      next      conn.close: set conn = nothing   %>

 

---

Microsoft Access 
 
In Microsoft Access, we can fall back on ADOX. 
 

<%       tbl = "table_name"      oldCol = "old_column_name"      newCol = "new_column_name"        cst = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="       cst = cst & server.mappath("/<path_to_database>.mdb")       set conn = CreateObject("ADODB.Connection")       conn.open cst         Set adox = CreateObject("ADOX.Catalog")      adox.ActiveConnection = Conn      adox.Tables(tbl).Columns(oldCol).Name = newCol      adox.Tables.Refresh      Set adox = nothing      conn.close: set conn = nothing  %>

 
If you give a name for a column that doesn't exist (or perhaps by running the script twice, since the old name won't exist after you've renamed it), you'll get this error: 
 

ADOX.Columns error '800a0cc1'   Item cannot be found in the collection corresponding to the requested name or ordinal.

 
Note that this method is only available when you connect to Access through the JET/OLEDB provider. If you use the Microsoft Access driver or an ODBC DSN, you will get the following error: 
 

ADOX.Column error '800a0cb3'   Object or provider is not capable of performing requested operation.