Home > Mobile >  Unable to get definition for Sales.vw_CustomerOrders view in AdventureWorks2017 sample database
Unable to get definition for Sales.vw_CustomerOrders view in AdventureWorks2017 sample database

Time:03-07

Looking at the AdventureWorks2017 database I ran across a view called Sales.vw_CustomerOrders. Wondering where it was getting its data I tried to query the object definition and the results came back NULL

SELECT definition
FROM   sys.sql_modules
WHERE  object_id = Object_id('Sales.vw_CustomerOrders')  

I noticed the icon is slightly different from the other views and SSMS won't allow you to generate a create statement either (only a drop). What is this particular view? Why can't you generate a create statement for it? How can I find out how it gets its data?

TIA

CodePudding user response:

As @MeyssamToluie mentioned in comments, view Sales.vw_CustomerOrders is not part of the out-of-box AdventureWorks database.

The different SSMS icon indicates the object is encrypted. When a view is created using WITH ENCRYPION option, the definition is obfuscated, not visible in the sys.sql_modules system view, and the CREATE cannot be scripted via normal methods. You'll find undocumented/unsupported ways to get the clear text definition of obfuscated objects with an internet search.

Use OBJECTPROPERTY to identify the encryption status of the view:

SELECT OBJECTPROPERTY(object_id, 'IsEncrypted') AS IsEncrypted, *
FROM sys.objects 
WHERE  object_id = OBJECT_ID(N'Sales.vw_CustomerOrders');
  • Related