Home

Columns Maintenance

 

Modifying a Column

  

When making a change on a column, you are also said to alter the table. To visually modify a column, from the Server Explorer, right-click its parent table and click Open Table Definition. Locate the column and change any characteristic you want: name, data type, nullity, or properties.

To programmatically support this operation, 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.

 

Adding a New Column

After a table has already been created, you can still add a new column to it.

To add a new column in Microsoft Visual Studio, in the Server Explorer, expand the connection to the desired database, expand the Tables node, and expand the table. Here Right-click the table and click Open Table Definition. To add a new column to the end of the table, click the first empty field under Column Name, type a name, and specify the other options.

To insert a new column between two existing one, right-click the column that will succeed it and click Insert Column:

Adding a New Column

This would create a new empty field. Type the desired name and specify the other options.

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:

private void btnDatabase_Click(object sender, EventArgs e)
{
    using (SqlConnection connection =
	new SqlConnection("Data Source=(local);" +
			  "Database='Exercise1';" +
			  "Integrated Security=yes;"))
    {
	SqlCommand command =
		 new SqlCommand("ALTER TABLE Customers " +
				"ADD EmaillAddress varchar(100);",
				connection);
	connection.Open();
	command.ExecuteNonQuery();

	MessageBox.Show(
		"A new column named \"EmailAddress\" has been added.");
    }
}

When this code is executed, a new column named Address, of type varchar, with a limit of 100 characters, that allows empty entries, will be added to a table named StaffMembers in the current database.

In Microsoft SQL Server Management Studio, you can use sample code to add a new column to a table. First display an empty query window and display the Templates Explorer. Expand the Table node. Under Table, drag Add Column and drop it in the query window. Delete the undesired sections of code and keep only the part that deals with adding a column. Here is an example:

--==========================================================================
-- Add column template
--
-- This template creates a table, then it adds a new column to the table.
--==========================================================================
USE <database, sysname, AdventureWorks>
GO

-- Add a new column to the table
ALTER TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
	ADD <new_column_name, sysname, column3>
	    <new_column_datatype,, datetime>
	    <new_column_nullability,, NULL>
GO

Renaming a Column

If you find out that the name of a column is not appropriate, you can change it.

To visually rename a column in Microsoft Visual Studio, in the Server Explorer, right-click the table that the column belongs to and click Open Table Definition. In the design view, highlight the name of the desired column to put it into edit mode and edit it.

In Microsoft SQL Server Management Studio, to change the name of a column, first open an empty query window. 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:

private void btnDatabase_Click(object sender, EventArgs e)
{
    string strConnection =
	"sp_rename 'StaffMembers.FullName', 'EmployeeName', 'COLUMN';";
    SqlConnection conDatabase = new 
	SqlConnection("Data Source=(local);" +
		      "Database='Countries2';" +
		     "Integrated Security=yes");
    SqlCommand cmdDatabase = new SqlCommand(strConnection, conDatabase);

    conDatabase.Open();

    cmdDatabase.ExecuteNonQuery();
    conDatabase.Close();
}sp_rename '', '', 'COLUMN'
GO

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.

Deleting a Column

If you have an undesired column that you don't want anymore in a table, you can remove it.

To visually delete a column, in the Server Explorer, right-click the table and click Open Table Definition. In the table, right-click the undesired column and click Delete Column:

Deleting a Column

You would not receive a warning.

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:

private void btnDatabase_Click(object sender, EventArgs e)
{
    using (SqlConnection connection =
	new SqlConnection("Data Source=(local);" +
			  "Database='Exercise1';" +
			  "Integrated Security=yes;"))
    {
	SqlCommand command =
		new SqlCommand("ALTER TABLE Customers " +
				"DROP Column DateIssued;",
				connection);
	connection.Open();
	command.ExecuteNonQuery();

	MessageBox.Show(
		"The column named \"DateIssued\" has been deleted.");
    }
}

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.

Microsoft SQL Server can also generate sample code you can use to delete a column from a table. In the Microsoft SQL Server Management Studio, before doing this, first display an empty query window and display the Templates Explorer. Expand the Table node. In the Table section, drag Drop Column and drop it in the query window. Delete the undesired sections of code and keep only the part that deals with adding a column. Here is an example:

--============================================
-- Drop column template
--
-- This template creates a table, then it  
-- drops one of the columns of the table.
--============================================
USE <database, sysname, AdventureWorks>
GO

-- Drop a column from the table
ALTER TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
	DROP COLUMN <new_column_name, sysname, column3> 
GO

Practical LearningPractical Learning: Setting Data Types

  1. In the Server Explorer, right-click Server.CPAR1.dbo and click Delete
  2. When asked for a confirmation, click Yes
 

Published on Monday 28 December 2007

 

Previous  Copyright © 2007 FunctionX, Inc. Home