Tuesday, February 14, 2012

Create View Syntax

Why can't one use a three part naming convention when creating a view?

example:

CREATE VIEW databasename.schemaname.anyview

AS

SELECT * FROM anytable

The following error is returned:

Msg 166, Level 15, State 1, Line 1

'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name.

You are creating the VIEW in the current database, therefore the three part name is not allowed since that would be creating a view in a different database.

You can, however, use the three part name for the table(s) used in the VIEW definition.

No comments:

Post a Comment