SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Introduction to Views – Day 23 of 35

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Introduction to Views - Day 23 of 35 joes2pros4 Answer simple quiz at the end of the blog post and –

Every day one winner from India will get Joes 2 Pros Volume 4.

Every day one winner from United States will get Joes 2 Pros Volume 4.

View Options

Not every query may be turned into a view.  There are rules which must be followed before your queries may be turned into views.

View Rules

This query includes a simple aggregation which totals the grant amounts according to each EmpID.  It’s a handy report, but we can’t turn it into a view. The error message shown displays when you attempt to run this code and create the view.  Notice that it says “…no column name was specified for column 2.”

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Introduction to Views - Day 23 of 35 j2p_23_1

We must first make certain this expression field column has a name before we can create this view. Alias the expression field as “TotalAmount” and then run this CREATE VIEW statement for vEmpGrantTotals.

Solarwinds
CREATE VIEW dbo.vEmpGrantTotals
AS
SELECT EmpID, SUM(Amount) AS TotalAmount
FROM [Grant]
GROUP BY EmpID


Encrypting Views

Suppose you want to make sure that people can utilize this view to run reports, but you don’t want them to be capable of seeing or recreating the underlying code.  The sp_HelpText system stored procedure reveals the code which created an object.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Introduction to Views - Day 23 of 35 j2p_23_2

We want to alter this view so that the source code is encrypted.  Two modifications to the code for vEmpGrantTotals will make this change:

1)        Change CREATE VIEW to ALTER VIEW.

2)        Add WITH ENCRYPTION before the AS keyword.

ALTER VIEW dbo.vEmpGrantTotals
WITH ENCRYPTION
AS
SELECT EmpID, SUM(Amount) AS TotalAmount
FROM [Grant]
GROUP BY EmpID

The best practice after we create or alter an object is to run a SELECT statement to confirm that it produces the expected result.  Look at Object Explorer and notice that a small padlock now appears on the icon for vEmpGrantTotals.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Introduction to Views - Day 23 of 35 j2p_23_3

Often times you can just right-click a view in Object Explorer and choose “Script View as” to see the code for the view.  Now when we attempt that maneuver for our encrypted view, SSMS gives us a message saying that the text is encrypted and we can’t script this view. Management Studio (SSMS) will not allow us to generate code for the encrypted view. The properties dialog for vEmpGrantTotals also tells us that the view is now encrypted.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Introduction to Views - Day 23 of 35 j2p_23_4

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Introduction to Views - Day 23 of 35 j2p_23_5

Attempt to run the sp_HelpText sproc and notice the message, “The text for object ‘dbo.vEmpGrantTotals’ is encrypted.”.

sp_helptext 'dbo.vEmpGrantTotals'

Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 54

The object ‘dbo.vEmpGrantTotals’ does not exist in database ‘JProCo’ or is invalid for this operation.

Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLProgrammingChapter4.2Setup.sql script from Volume 4.

Question 23

What are the two ways to see the code that created a view? (Choose Two)

  1. WITH SCHEMABINDING
  2. WITH ENCRYPTION
  3. sp_helptext
  4. sp_depends
  5. sys.syscomments

Rules:

Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Every day one winner from India will get Joes 2 Pros Volume 4.
Every day one winner from United States will get Joes 2 Pros Volume 4.
The contest is open till next blog post shows up at which is next day GTM+2.5.

Reference:  Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – All about SQL Constraints – Day 22 of 35
Next Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Easy Introduction to CHECK Options – Day 24 of 35

Related Posts

102 Comments. Leave new

  • Sudeepta Ganguly
    August 24, 2011 1:34 am

    The correct answers are:
    3. sp_helptext
    5.sys.syscomments

    Sudeepta,
    India.

    Reply
  • vinay (@vinayprasadv)
    August 24, 2011 1:56 am

    Answer is:

    3.sp_helptext
    5.sys.syscomments

    Vinay
    Pune,India

    Reply
  • What if we encrypted it then i myself want to change the view code ? any way to decrypt it back or else i’ll always need a readable backup copy .

    Namaste (regards)!
    Anugrah

    Reply
  • 1) Using sp_helptext ‘vEmpGrantTotals’
    should not use scheema.viewname.

    2) In Object explorer –> database –> Views–> Right click on view and choose “Script View as” to see the code for the view

    Bablu
    INDIA

    Reply
  • Question no 23.
    Correct Answer 3 & 5 [sp_helptext & sys.syscomments]

    Description :–
    Sys.syscomments :– This is a system view which use other different system objects.

    sp_helptext :: is used to show the definition of store procedure and view. If you see the definition of sp_helptext, then you find that this system store procedure is using sys.syscomments system view.

    Vinay

    Reply
  • Correct Answers are : 3 & 5

    Because.,

    Sp_helptext:
    Displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, user-defined Transact-SQL function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure.

    sys.syscomments:
    Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements.

    Raju,
    India.

    Reply
  • The correct answers are:
    3. sp_helptext
    5.sys.syscomments

    Jitendra
    Copenhagen,Denmark

    Reply
  • Pinal,

    Correct Answers are 3(sp_helptext) & 5(sys.syscomments)

    Desc:
    sys.syscomments:-
    select [text] from syscomments where id = OBJECT_ID(”)
    sp_helptext:-
    sp_helptext ”
    Otherways are:
    1. GOTO Object Explorer –> Database –> Views–> Right click on view and select “Script View as” to find the vode of the view
    2. select VIEW_DEFINITION from information_schema.VIEWS where TABLE_NAME =”

    Location: India

    Reply
  • Sayli Deshmukh (@sddesh)
    August 24, 2011 9:57 pm

    Answers are 3 and 5.

    SDDesh,
    USA

    Reply
  • Answers: Option 3 and 5
    If it is not encrypted you can get the code definition from sys.syscomments and/or sp_helptext.
    Additionally you can get it from the GUI by right clicking the view and selecting to script it out.

    Reply
  • Option 3 and 5 are correct:

    3. sp_helptext – this system stored procedure reveals the code which created an object.

    5. sys.syscomments – Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements.

    Additionally using SSMS you can right-click on the view and click “script view as” and choose “create to” and then “new query editor window” to see the code used to create the view.

    Country of Residence: USA

    Reply
  • Shubhangi Hirudkar
    August 26, 2011 2:38 pm

    The correct options are
    3.sp_helptext
    5.sys.syscomments

    Reply
  • Q 23) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Introduction to Views – Day 23 of 35

    A.) 3 and 5

    Winner from USA: Naomi

    Winner from India: Nakul Vachhrajani

    I thank you all for participating here. The permanent record of this update is posted on facebook page.

    Reply

Leave a Reply

Menu