Sql server® 2012 t-sql fundamentals

 

CHAPTER 5

 

Table Expressions

 

 173

Before you look at the ENCRYPTION option, run the following code to alter the definition of the 

USACusts view to its original version.

ALTER VIEW Sales.USACusts 

AS 

 

SELECT 

  custid, companyname, contactname, contacttitle, address, 

  city, region, postalcode, country, phone, fax 

FROM Sales.Customers 

WHERE country = N'USA'; 

GO 

To get the definition of the view, invoke the OBJECT_DEFINITION function like this.

SELECT OBJECT_DEFINITION(OBJECT_ID('Sales.USACusts'));

The text with the definition of the view is available because the view was created without the 

ENCRYPTION option. You get the following output.

CREATE VIEW Sales.USACusts 

AS 

 

SELECT 

  custid, companyname, contactname, contacttitle, address, 

  city, region, postalcode, country, phone, fax 

FROM Sales.Customers 

WHERE country = N'USA'; 

Next, alter the view definition—only this time, include the ENCRYPTION option.

ALTER VIEW Sales.USACusts WITH ENCRYPTION 

AS 

 

SELECT 

  custid, companyname, contactname, contacttitle, address, 

  city, region, postalcode, country, phone, fax 

FROM Sales.Customers 

WHERE country = N'USA'; 

GO 

Try again to get the text with the definition of the view.

SELECT OBJECT_DEFINITION(OBJECT_ID('Sales.USACusts'));

This time you get a NULL back.

As an alternative to the OBJECT_DEFINITION function, you can use the sp_helptext stored proce-

dure to get object definitions. For example, the following code requests the object definition of the 

USACusts view.

EXEC sp_helptext 'Sales.USACusts';

www.it-ebooks.info

---

174  

Microsoft SQL Server 2012 T-SQL Fundamentals

Because in our case the view was created with the ENCRYPTION option, you will not get the object 

definition back, but instead you will get the following message.

The text for object 'Sales.USACusts' is encrypted.