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 Pinal,
The correct options are 3 and 5
3. sp_helptext Displays the definition of a user-defined view or system object such as a system stored procedure.This gives the code thar created the view.
5. sys.syscomments Contains entries for each view,stored procedure within the database. This should be used with a join with sys.sysobjects to get the code that created a view.
Thanks & Regards
Santosh.S
Bangalore, India
At Present, according to me, code that created a view, can be seen using “sp_helptext” and “sys.syscomments”
sp_helptext displays the definition of a user-defined rule, unencrypted Transact-SQL stored procedure/ view, user-defined Transact-SQL function, trigger, computed column, CHECK constraint,or system stored procedure.
Where as,
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. In SQL server 2008, its mapped as “sys.sql_modules”
Syntax for sp_helptext is as follows,
USE [database_name];
GO
EXEC sp_helptext ‘[view_name]’;
GO
Syntax for sys.sql_modules is as follows,
USE [database_name];
GO
SELECT sm.object_id
FROM sys.sql_modules AS sm
GO
Other than the options given above, one can also see the code by right clicking the object explorer and selecting ‘script view’.
COUNTRY – INDIA
The Correct Option is
# 3. I didn’t find second one. Pinal please tell me second one.
Country : India
Correct Options are
3.sp_helptext
5.sys.syscomments
CREATE VIEW dbo.vEmpGrantTotals
AS
SELECT EmpID, SUM(Amount) AS TotalAmount
FROM [Grant]
GROUP BY EmpID
For sp_helptext
=============
sp_helptext vEmpGrantTotals
For sys.syscomments
=================
SELECT DISTINCT c.text
FROM syscomments c
inner JOIN sysobjects o ON
c.[id] = o.[id]
WHERE o.[name] = ‘vEmpGrantTotals’ AND o.TYPE =’V’
Thanks,
Mitesh Modi
Gujarat, India
3 and 5 are the correct answers.
Sreeram
India
Hi Pinal,
Correct Options are 3 & 5.
3. sp_helptext
5. sys.syscomments
Thanks & Regards
Neeraj Bansal
Noida
Correct Answer option No : 2 & 3
Shekhar Gurav.
Country : India
Option 3 & 5 are correct
These two options, sp_helpText and sys.syscomments returns the text unless its encrypted.
(Sale, Nigeria)
The correct Answers are :3.sp_helptext, 5. sys.syscomments
3.sp_helptext ‘[view_name]‘
there is no easy way to use the result of this query.
5. sys.syscomments
The disadvantage here is that this method is lengthy and you have to specify the object name and schema name separately.
Richardson, Texas, USA
Correct Answer is option 3 & 5.
3. sp_helptext
5. sys.syscomments
sp_helptext: We can use this to display the the definition of a View.
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.
sys.sysobjects.name – Displays the View Name.
sys.syscomments.text – Displays the definition of the View.
Malay Shah
Ahmedabad, India
Option 3 and 5 are correct
Robin Thomas
India
Something must have gone wacky with this email: It has the same title as yesterdays email (Day 22 of 35 not Day 23 of 35), this question is about View Options, at the bottom of the email it shows Question 23 along with a question about views (What are two ways to see the code that created a view? (Choose two)), but the options to choose from came from the Check Constraint question from day 22 of 35.
Since I have no real options to choose from I will also choose options 3 & 5
USA
Mike Michalicek
Answers 3 and 5 will work.
You can also use sys.sql_modules with code to the effect of:
SELECT definition
FROM sys.sql_modules
WHERE object_id = Object_id(”)
Matt Nelson, USA
Options 3 & 5.
Reagrds,
Girish Rokade
Pune India
The Correct answers are option 3 & 5.
Examples,
We we take view1 as a view definition.
then,
sp_helptext View1
SELECT text FROM sys.syscomments WHERE id = OBJECT_ID(‘View1’);
Regards,
Girish Rokade
Pune India
Answers are 3 and 5.
Additionally you can also use “Script View as” which is what I’ve typically done. However, none of these methods will work once encryption is turned on. It is a way to protect others from viewing the code.
USA
The correct answers are options 3 and 5 to view the source code of a non-encrypted view.
Country: United States
Hi Pinal,
Challenge:
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
Correct Answer with Explanation:
The correct choices listed abobe to see the code that created a view are #3 and #5: using sp_helptext and by running a query against sys.syscomments. The view cannot be encrypted, though.
Country:
United States
Thanks for the knowledge!
Regards,
Bill Pepping
Hi everyone
The answer is 1 and 5
sp_helptext
or
select text from sys.siscomments where text like ‘%<view_name%'
from: Chile
Answer to the above question is :
3.sp_helptext
5.sys.syscomments
Why option 3 :
Sp_helptext is a system stored procedure, which is used to view the contents of the objects created in SQL Server. Contents of objects such as unencrypted stored procedure, user defined function, view, trigger etc. etc. can be displayed using above mentioned SSP.
To view the contents of view using sp_helptext
sp_helptext
Why option 5 :
sys.syscomments contains information / entry regarding all objects those are getting created in SQL Server. Objects such as sp name, functions, views, check constaints, default constaints, rules, triggers etc. etc. The definition of all the above mentioned objects is stored in Name column of sys.syscomments.
To view the contents of view using sys.syscomments
select name,text from sys.syscomments a
inner join sys.objects b on a.id = b.object_id
where b.name = ‘view_name’ and b.xtype=’V’
Kedar Dighe.
Country of Residence – India