To create a view, use the TRANSACT-SQL (T-SQL) CREATE VIEW
statement in the following form:
CREATE VIEW [databasename].[owner].viewname[
(column [n, ...])]
[WITH attribute [n, ...]]
[WITH CHECK OPTION]
Everything's optional except viewname and the columns that comprise it.
After you've created the view, users can execute it via a SELECT statement in the following form:
SELECT *|fieldlist FROM database.owner.viewname
As long as the view is in the current database, you can omit database. However, if you want to view data in another database, the statement needs the explicitly declared database argument as shown in Figure 1. From inside a database named Sales, the SELECT statement executes a view named vUpdateProductionLocation in AdventureWorks (a sample database that you can download). It really is that easy.
|Figure 1. View Data in Another Database: Views enable you to identify another database explicitly to execute an existing view.|
The actual view is a simple SELECT statement that displays three fields of data from Production.Location, a table in AdventureWorks:
SELECT Name, CostRate, Availability
Only the second SELECT (see Figure 1) returns data, because that statement specifies the database AdventureWorks. That's the key to viewing data in another database, the easy way. It's so simple that you might not realize you can do it!
The view specifies the table and columns, and it can include a WHERE clause to limit the results. The calling SELECT statement identifies the database and the view.