Viewing Table Properties

Knowing more about your tables is extremely important, whether they are new tables or decades old. These details are very easy to find by using SQL Server Management Studio.

Below are steps to finding out more about your tables:

  1. Launch the SQL Server Management Studio.
  2. Connect to the appropriate SQL Server instance.
  3. Expand the connection’s entry in the Object Explorer view.
  4. Expand the Databases folder.
  5. Expand the Tables folder, and then right-click the table you want to examine.

  1. Choose the Properties option from the menu, as shown below: note that the database, server, and user were blocked for security reasons

On the left-hand side of the screen, you can see five essential paths to navigate to. You have the General, Permission, Change Tracking, Storage, and Extended Properties options. The Extended Properties tab is where you can define and maintain your customized properties.

How to Create Views

Although views aren’t tables in the real sense, they help to focus, simplify, and customize the perception the user has of the database. Views provides a backward-compatible framework to mimic a table whose schema has changed.

Views acts as a security mechanism by allowing users to access data through the view, without granting the permissions to directly access the underlying base tables.

Steps to creating a view are shown below:

1. Launch the SQL Server Management Studio.

2. Connect to the appropriate SQL Server instance.

3. Expand the connection’s entry in the Object Explorer view.

4. Expand the Databases folder.

5. Right-click the Views folder and choose New View.

This procedure is shown in the view below, which I have named Apple. I had to block some writings for security reasons:

From the above New View, you then select the table you want to make up your view. You can hold down the CTRL key to select multiple choices. In this case, we create a view that returns a list of all Partners and the Date they were created. I made use of tables already created in the database. They include the partners, valueTx, accountTx, and Txrefs tables. When your selection is made, you can click on the “Add” button. When you’ve finished choosing the objects that will make up your view, click Close. When you’re satisfied with your work, click the disk icon to save it.

The four tables that make up the view Apple are illustrated in the diagram below:

A view with its relationships, columns, and SQL statement defined as above. Note that some areas are covered with blue for security and privacy reasons.

The diagram below shows how to explore a view in the SQL Server Management Studio.

Creating a View Using Transact SQL in MSSQL Server

Below is the syntax for creating views:

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] 
[ WITH  [ ,...n ] ] 
AS select_statement 
[ ; ]
    [ VIEW_METADATA ]     

schema_name is the name of the schema to which the view belongs. view_name is the name of the view. View names must follow the rules for identifiers. Specifying the view owner name is optional. Column is the name to be used for a column in a view. A column name is required only when a column is derived from an arithmetic expression, a function, or a constant; when two or more columns may otherwise have the same name, typically because of a join; or when a column in a view is specified with a name different from that of the column from which it is derived. Column names can also be assigned in the SELECT statement. If column is not specified, the view columns acquire the same names as the columns in the SELECT statement.

See this detailed example of how a view looks:

CREATE VIEW [dbo].[Apple] as
select p.msisdn as msisdn, a.openingbalance as openingbalance, min (a.datecreated) as datecreated
from accounttx a, valuetx v, partners p, txrefs t
where p.msisdn = v.origmsisdn
and v.txrefid = a.txrefid
and t.txrefid = a.txrefid
and p.partnerid = a.partnerid
and v.txrefid = t.txrefid
and t.txstatusid = 0
and p.msisdn in ('23480******',
and a.datecreated >= '2011-07-01' and a.datecreated < '2011-07-08'
group by p.msisdn, a.openingbalance, a.datecreated