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');