The Tables of a Database
As we saw when studying data sets, a table is one or more lists of items. If the table is only one list of items, these items are identical in nature. Here is an example:
If the table is made of more than one list, each list is made in a distinguishable category. Here is an example:
Based on this, a list is simply an arrangement of information and this information, also called data, is stored in tables.
The information of a table is organized in categories called columns. To visually create a table in Microsoft SQL Server, first open the SQL Server Management Studio. In the Object Explorer, locate and expand the database for which you are creating the table. Then right-click the Tables node and click New Table. A window would come up and wait for you to proceed:
To visually create a table in Microsoft Visual Studio, in the Server Explorer, expand the connection to the database that will own the table. Right-click the Tables node and click Add New Table:
After clicking Add New Table, a new empty table would come up and expect you to take charge:
To complete the creation of a table, you must save it. If you are freshly creating a table and decide to save it, you would be prompted to name it. The name of a table:
Besides these rules, you can make up yours. To avoid confusion, here are the rules we will use to name our tables:
In SQL, to create a table, you start with the following statement:
CREATE TABLE TableName;
The CREATE TABLE expression is required. The TableName factor specifies the name of the new table. The TableName can use the rules and suggestions we reviewed for the tables.
To assist you with creating a table, Microsoft SQL Server can generate sample code for you. You can then simply modify or customize it. In Microsoft SQL Server Management Studio, first display or open an empty query window. To display the Templates Explorer, on the main menu, you can click View -> Templates Explorer. In the Templates Explorer, expand the Table node. Under table, drag Create Table and drop it in the query window. Sample code would be generated for you.
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.
Like every other object of a database or of the computer, a table possesses some characteristics that are proper to it. To view these characteristics in Microsoft SQL Server Management Studio, in the Object Explorer, you can right-click the table and click Properties.
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 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:
Private Sub Exercise_Load(ByVal sender As Object, _ ByVal e As System.EventArgs) _ Handles Me.Load Using connection As SqlConnection = _ New SqlConnection("Data Source=(local);" & _ "Database='AltairRealtors1';" & _ "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
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
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:
|Home||Copyright © 2008-2012 FunctionX|