Go to the Views sub-node and double-click. You'll see the code behind the view complete with color-coding.

Unfortunately, we don't always have the option of having EM around to hold our hand through this stuff. The bright side is that we have two ways of getting at the actual view definition:

• sp_helptext
• The syscomments system table

Using sp_helptext is highly preferable, as when new releases come out, it will automatically be updated for changes to the system tables.

Let's run sp_helptext against one of the supplied views in the Northwind database - Alphabetical List of Products:

EXEC sp_helptext [Alphabetical list of products]

SQL Server obliges us with the code for the view:

Text
-------------------------------------------------------------
create view "Alphabetical list of products" AS
SELECT Products.*, Categories.CategoryName
FROM Categories INNER JOIN Products ON Categories.CategoryID = 
Products.CategoryID
WHERE (((Products.Discontinued)=0))

I must admit to finding this one of the more peculiar examples that Microsoft supplies - I attribute it to this database being migrated from Access. Why? Well, the one seemingly simple thing that we cannot do in views is use an ORDER BY clause. The exception to the rule on the ORDER BY clause is that you can use ORDER BY as long as you also use the TOP predicate. Microsoft say this view is in alphabetical order, but there is no guarantee of that. Indeed, if you run the query, odds are it won't come out in alphabetical order!

Note that the restriction on using the ORDER BY clause only applies to the code within the view. Once the view is created, you can still use an ORDER BY clause when you reference the view in a query.

Now let's try it the other way - using syscomments. Beyond the compatibility issues with using system tables, using syscomments (and most other system tables for that matter) comes with the extra added hassle of everything being coded in object IDs.

Object IDs are SQL Server's internal way of keeping track of things. They are integer values rather than the names that you're used to for your objects.

Fortunately, you can get around this by joining to the sysobjects table:

SELECT sc.text 
FROM syscomments sc
JOIN sysobjects so
 ON sc.id = so.id
WHERE so.name = 'Alphabetical list of products'

Again, you get the same block of code (indeed, all sp_helptext does is run what amounts to this same query):

 
Text
-----------------------------------------------------------------
create view "Alphabetical list of products" AS
SELECT Products.*, Categories.CategoryName
FROM Categories INNER JOIN Products ON Categories.CategoryID = 
Products.CategoryID
WHERE (((Products.Discontinued)=0))

(1 row(s) affected)

I can't stress enough my recommendation that you avoid the system tables where possible - but I do like you to know your options.