Solved

VB.net SQL Rename a table

Posted on 2013-10-28
3
969 Views
Last Modified: 2013-10-29
Hi

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 & "]"


            myConnection.Open()

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

            DropColumnSQL = True

            myConnection.Close()
0
Comment
Question by:murbro
3 Comments
 
LVL 6

Assisted Solution

by:ButlerTechnology
ButlerTechnology earned 250 total points
ID: 39605300
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



Tom
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 250 total points
ID: 39605939
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.
0
 

Author Closing Comment

by:murbro
ID: 39608178
Thanks very much
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now