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.”
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.
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.
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.
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.
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)
- WITH SCHEMABINDING
- WITH ENCRYPTION
- sp_helptext
- sp_depends
- 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)
102 Comments. Leave new
Hi,
The options given do not seem to fit, so I’ll go ahead and give you answers from your post. The two ways to view the code that created a view are:
1. Run a SCRIPT VIEW AS –> CREATE TO command
2. Run the system stored procedure sp_helptext on the view with the syntax sp_helptext ” . Based on the scenario, we can choose to include or exclude the schema prefix.
Ramakrishnan RS
Mysore, India
Q.No 23 : What are two ways to see the code that created a view? (Choose two)
1) Using sp_helptext
2) In Object explorer, Right click a view and choose “Script View as” to see the code for the view.
Note: i think today Quiz answers are not related to question. Its same like as yesterday Quiz. please, review it.
Chennai, Tamilnadu, India
Hi Pinal ,
Are the options correct ?
Nagaraj
Hi Pinal,
Looks like questions & answers of Day 22 & 23 are mixed up. Please fix it.
Thanks
Sudhir
Hi Pinal,
This should be « SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – All about SQL Constraints – Day 23 of 35 whereas its marked as 22.
Topic is View whereas Question is on constraint. Please take a look & fix this.
Thanks
Sudhir
Still Questions for both Day 22 & 23 are same. Please see.
Hi,
Option 3 & 5 are correct as sp_helpText returns the text unless its encrypted. and sys.syscomments.
for example
select text from sys.syscomments a
inner join sys.objects b on a.id = b.object_id
where b.name = ‘viewname’ –view is the name of the view
sp_helptext ‘viewname’
Thanks
Sudhir Chawla
New Delhi, INDIA
If the view is not encrypted, you can see its definition using sp_helptext SP or sys.syscomments.
So, the correct options are 3 and 5.
I am from USA
Correct answers are:
* Use sp_helptext ‘dbo.viewName’
* right-click a view in Object Explorer and choose “Script View as”
Rene Castro
El Salvador
I think the title of the Day is wrong. The post is about views, not about constraints. The content of the page and questions seem to be OK, but the title doesn’t look correct to me.
Also, this text didn’t mention sys.syscomments, so I just ran a query
select * from sys.syscomments to make sure it included the view definition.
Only option 3 is correct. Other option is to use the Ui to generate the script using ssms. Right click the view and click Create script to new query editor window
Sathya
India
Answer is correct=3)sp_helptext
krishan kumar mishra
India
Hello PInal,
The correct answers are;
3. sp_helptext
5. sys.syscomments
Bulent
Answer For Q.23 are 3 & 5
1) Using sp_helptext
2) In Object explorer, Right click a view and choose “Script View as” to see the code for the view.
Question 23
Ans : sp_helptext
Chennai, INDIA
Q.No 23 : What are two ways to see the code that created a view? (Choose two)
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.
Raghunath.G.R
Bangalore, India
Hi,
sp_helptext displays the view definition.
In addition,
sys.syscomments itself is a view.
sp_helptext ‘sys.syscomments’
India
If not use Encryption in view
we can see code using sp_helptext
WITH SCHEMABINDING is used to binds a view to the schema of the tables.
WITH ENCRYPTION used to encrypt a view or sps.
sp_depends := Displays information about database object dependencies, such as the views and procedures that depend on a table or view.
sys.syscomments := Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database.
So Only one option is correct ie., option 3. sp_helptext
Nikhildas
Cochin
INDIA
Hi ,
Correct Option is 2 and 3.
I am from Gujarat,India