• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1237
  • Last Modified:

VB.net SQL Rename a table


What VB.net code would I use to rename an SQL table using
SQL Client style of coding similar to the following:

            Dim myConnection As SqlConnection = New SqlConnection(Globals.ThisAddIn.oRIGHT.lblConnectionString.Text)
            Dim myCommand As SqlCommand

            Dim ra As Integer

            Dim sSQL As String = "ALTER TABLE [" & oTable & "] DROP COLUMN [" & oColumn & "]"


            myCommand = New SqlCommand(sSQL, myConnection)
            ra = myCommand.ExecuteNonQuery()

            DropColumnSQL = True

Murray Brown
Murray Brown
2 Solutions
You will want to use a stored procedure to rename a table.  Here's an example:
Exec sp_rename Old Name, New Name

Open in new window

Jacques Bourgeois (James Burger)PresidentCommented:
Since it is usually something you do only once, I would not use a stored procedure. And if you are able to create a store procedure, you are also able to rename the table "manually".

Simply use the SQL command provided by ButlerTechnology in your sSQL variable.

Or you might prefer to work with the Microsoft.SqlServer.Management.Smo library, that has been designed specifically to work with the server, while SqlSlient was designed for data access.

Dim srv AsServer srv = New Server("(local)")
Dim db As Database = srv.Databases("AdventureWorks2012");
Dim tb as Table  = New Table(db, "Test Table");
tb.Rename("New Name")

Any way you chose, be aware that it won't work if the user does not have the proper rights on the database.

And also, the rename does not trigger down to whathever was already created to work with the table, such as views, stored procedures, and naturally, external code that already works on the table.
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now