Table maintenance consists of reviewing or changing
its aspects. This includes reviewing the list of tables of a database,
renaming a table, or deleting it.
In Microsoft Visual Studio, to see the list of tables of
a database, in the Server Explorer, expand the connection to the desired
database and expand the Tables node. Here is an example:
Using Microsoft SQL Server Management Studio, to see the list of tables of a database using SQL, in
a Query window, specify the database (using a USE statement), and execute sp_help (it is a stored procedure).
Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Load
Using connection As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Database='Exercise';" & _
"Integrated Security=yes;")
Dim command As SqlCommand = _
New SqlCommand("sp_help", connection)
connection.Open()
Dim rdr As SqlDataReader = command.ExecuteReader()
While rdr.Read()
lbxTables.Items.Add(rdr(0))
End While
rdr.Close()
End Using
End Sub
End Class
If you find out that the name of a table is not
appropriate, you can change it. To change the name of a table with code, execute sp_rename,
followed by the current name of the table, a comma, and the new desired
name of the table. The formula to use is:
sp_rename ExistingTableName, TableNewName;
The names of tables should be included in
single-quotes. Here is an example:
sp_rename 'StaffMembers', 'Employees'; GO
In this case, the interpreter would look for a table
named StaffMembers in the current or selected database. If it finds it, it
would rename it Employees. If the table does not exist, you would receive
an error.
If you have an undesired table in a database, you can
remove it. To delete a table using SQL, use the following
formula:
DROP TABLE TableName
The DROP TABLE expression is required and it is
followed by the name of the undesired table. When you execute the
statement, you will not receive a warning before the table is deleted.
There are three main ways you can refer to a table.
To refer to, or to indicate, a table:
When making a change on a column, you are also said to
alter the table. To programmatically change a column, the SQL starts with the following
formula:
ALTER TABLE TableName
When using this statement, the ALTER TABLE
expression is required and it is followed by the name of the table.
After a table has already been created, you can still
add a new column to it.
In SQL, the basic formula to add a new column to an
existing table is:
ALTER TABLE TableName ADD ColumnName Properties
The ColumnName factor is required. In fact, on
the right side of the ADD operator, define the column by its name and use
all the options we reviewed for columns.
Here is an example:
Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Load
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Database='Exercise';" & _
"Integrated Security=yes;")
Dim Cmd As SqlCommand = _
New SqlCommand("ALTER TABLE Customers " & _
"ADD EmaillAddress nvarchar(50);", _
Connect)
Connect.Open()
Cmd.ExecuteNonQuery()
MsgBox("A new column named ""EmailAddress"" has been added.")
End Using
End Sub
End Class
When this code is executed, a new column named Address,
of type nvarchar, with a limit of 50 characters, that allows
empty entries, will be added to a table named StaffMembers in the current
database.
If you find out that the name of a column is not
appropriate, you can change it.
In Transact-SQL, in a query
window, execute sp_rename using the following
formula:
sp_rename 'TableName.ColumnName', 'NewColumnName', 'COLUMN'
The sp_rename factor and the 'COLUMN'
string are required. The TableName factor is the name of the table
that the column belongs to. The ColumnName is the current name of
the column. The NewColumnName is the desired name you want to give
to the column.
Here is an example:
Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Load
Dim strConnection As String = _
"sp_rename 'Customers.DateExpired', 'ExpirationDate', 'COLUMN';"
Dim Connect As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Database='Exercise';" & _
"Integrated Security=yes")
Dim cmdDatabase As SqlCommand = _
New SqlCommand(strConnection, Connect)
Connect.Open()
cmdDatabase.ExecuteNonQuery()
MsgBox("The DateExpired column has been renamed to ExpirationDate")
Connect.Close()
End Sub
End Class
When this code is executed, the interpreter will look
for a column named FullName in the StaffMembers table of the current or
selected database. If it finds that column in the table, then it renames
it EmployeeName.
If you have an undesired column that you don't want
anymore in a table, you can remove it.
To programmatically delete a column, use the following formula:
ALTER TABLE TableName DROP COLUMN ColumnName
On the right side of the ALTER TABLE
expression, type the name of the table. On the right side of the DROP
COLUMN expression, enter the name of the undesired column. Here is an
example:
Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Load
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Database='Exercise';" & _
"Integrated Security=yes;")
Dim Cmd As SqlCommand = _
New SqlCommand("ALTER TABLE Customers " & _
"DROP Column DateIssued;", _
Connect)
Connect.Open()
Cmd.ExecuteNonQuery()
MsgBox("The column named ""DateIssued"" has been deleted.")
End Using
End Sub
End Class
When this code is executed, the interpreter
will look
for a column named CurrentResidence in a table named StaffMembers
of the current. If it finds that column, it will remove it from the
table.
|
|
|||||||||||||||||||||||
|
|
The Tables of a Database
Subscribe to:
Post Comments (Atom)
Related Scripting
No comments:
Post a Comment