Viewing the Properties of a Table

Most operations require that you open a table before using it. There are various ways a table displays, depending on how you want to examine it:

• In Microsoft SQL Server Management Studio:
  • To view the structure of a table, perhaps to change its columns, in the Object Explorer, expand your database and its Tables node. Right-click the table and click Modify. The table would open in design view, the same view you use to visually create a table.
  • If you want to view the SQL code of a table, in the Object Explorer, right-click the table, position the mouse on Script Table AS, CREATE To, and click New Query Editor Window
  • To open a table to view its data, perhaps to perform data entry, in the Object Explorer, right-click the table and click Open Table
• In Microsoft Visual Studio, in the Server Explorer, expand the connection to the desired database and expand its Tables node:
  • To view the structure of a table, right-click the table and click Open Table Definition
  • To open a table to view its data, right-click the table and click Show Table Data

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). Here is an example:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Exercise
{
    public partial class Exercise : Form
    {
	public Exercise()
	{
	    InitializeComponent();
    	}

    	private void Exercise_Load(object sender, EventArgs e)
    	{
	    using (SqlConnection connection =
		new SqlConnection("Data Source=(local);" +
			"Database='AltairRealtors1';" +
			"Integrated Security=yes;"))
	    {
	    	SqlCommand command =
			new SqlCommand("sp_help", connection);
	        connection.Open();
	    	SqlDataReader rdr = command.ExecuteReader();

	    	while (rdr.Read())
	    	{
		    lbxTables.Items.Add(rdr[0].ToString());
	    	}

	        rdr.Close();
	    }
    	}
    }
} 

If you find out that the name of a table is not appropriate, you can change it. To change the name of a table in the SQL Server Management Studio, in the Object Explorer, right-click the table and click Rename. Type the desired name and press Enter.

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

•  In the SQL Server Management Studio, in the Object Explorer, right-click the table under its database node and click Delete
• In Microsoft Visual Studio, in the Server Explorer, expand the connection to the database that owns the table and expand its Tables node. Right-click the undesired table and click Delete

You will receive a warning giving you a chance to confirm your intentions. If you still want to remove the table, click OK.

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.

You can also use sample code in Microsoft SQL Server Management Studio that can generate code for you. First display an empty query window. Also display the Templates Explorer and expand the Table node. Under Table, drag Drop Table and drop it in the empty query window. Sample code would be generated for you. You can then simply modify it and execute the statement.

In future lessons, we will write various expressions that involve the names of tables. In those expressions, you will need to specify a particular table you want to use. There are three main ways you can do this. To refer to, or to indicate, a table:

• You can simply type its name. An example would be Students
• You can type dbo, followed by the period operator, followed by the name of the table. An example would be dbo.Students
• You can type the name of the database to which the table belongs, followed by the period operator, followed by dbo, followed by the period operator, and followed by the name of the table. An example would be RedOakHighSchool.dbo.Students